Query SQL condition if

Asked

Viewed 436 times

1

I have a problem in doing a query where I have 3 fields and just one of them is filled that no longer shows this result.

Someone can help me do that?

select * from tabelas where campo1 and campo2 and campo3
sql query

  • And php? could put the true text.

  • 1

    In part where campo1 and campo2 and campo3 you have to put some comparison, for example, campo1 == 10, if in fact you just wanted to select these fields, then they enter the select and not in the where. For example, select campo1, campo2, campo3 from tabelas.

  • You mean "just one of them is NOT filled" right? Missed to say what is the result you want.

  • As Carlos said, you are probably confusing the fields... try this way: select campo1, campo2, campo3 from tabelas

2 answers

3

If you want everyone to be shown unless one is filled do so:

SELECT *
FROM tabelas
WHERE campo1 IS NULL
  AND campo2 IS NULL
  AND campo3 IS NULL

If you want where all are filled use IS NOT NULL:

SELECT *
FROM tabelas
WHERE campo1 IS NOT NULL
  AND campo2 IS NOT NULL
  AND campo3 IS NOT NULL

1

You can use the function COALESCE to identify whether all fields are null (combined with is null), or if any of them are filled (is not null):

select * from tabelas where coalesce(campo1, campo2, campo3) is null;

select * from tabelas where coalesce(campo1, campo2, campo3) is not null;

Example in SQL Fiddle

  • 1

    Is there any performance gain, comparing to Andrey’s response?

  • I found nothing about it, but it depends on the implementation of COALESCE in the database used. In SQL Server, for example, the COALESCE is converted into case when internally, so there may be gain depending on the data model. For example, if the campo1 has an index and the other fields do not, in cases where campo1 is filled in the SQL Server engine and would not need to check the other fields, since the case would be interrupted in the first field.

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.