How do I SELECT on 2 or more Tables with 2 or more conditions?

9

I have a problem making a select with the following conditions: I want to display the data with either condition1 or condition2

Select * tabela inner join tabela2 WHERE campo = 0 and campo2 = '' and campo3 or campo10 is NULL

Select all the fields of the table where condition1 or condition two.

Concrete example:

select * from tb_detalhe_trabalhador inner join tb_trabalhador on tb_detalhe_trabalhador.id = tb_trabalhador.id inner join tb_equipamentos on tb_detalhe_trabalhador.id = tb_equipamentos.id where AlvaraNumero = 0 and (AlvaraValidade='' or AlvaraValidade is Null or AlvaraValidade='0000-00-00') and
 (AlvaraAnexo='' or AlvaraAnexo is Null) and AcidenteNumero = 0 and (AcidenteValidade='' or AcidenteValidade is Null or AcidenteValidade='0000-00-00') and (AcidenteAnexo='' or AcidenteAnexo is Null) and SeguroNumero = 0 and (SeguroValidade='' or SeguroValidade is Null or SeguroValidade='0000-00-00' ) and (SeguroAnexo='' or SeguroAnexo is Null) and 
InstaladorNumero = 0 and (InstaladorValidade='' or InstaladorValidade is Null or InstaladorValidade='0000-00-00') and (InstaladorAnexo='' or InstaladorAnexo is Null)
//Quero acrescentar esta segunda condição
or MedicaValidade is NULL or MedicaAnexo is NULL or ProjectistaNumero is NULL or 
 ProjectistaValidade is NULL or ProjectistaAnexo is NULL or GasNumero is NULL or GasValidade is NULL or GasAnexo is NULL or SoldadorNumero is NULL or SoldadorValidade is NULL or SoldadorAnexo is NULL or MecanicoValidade is NULL or MecanicoNumero
 is NULL or MecanicoAnexo is NULL or ClasSoldadorNumero Is NULL or ClasSoldadorValidade is NULL or ClasSoldadorAnexo is NULL order by tb_trabalhador.id 
    
asked by anonymous 11.03.2014 / 11:14

1 answer

25

Your concrete example is a bit confusing and seems to have little to do with the logic you are presenting.

Your logical example

Since you are not presenting details about each table and how they relate, I can leave some tips to build the query based on the logic presented:

  

Select * inner table join table2 WHERE field = 0 and field2 = '' and field3 or field10 is NULL

You should give an alias to the tables to correctly identify where the columns are:

SELECT t1.* FROM minhaTabela t1 WHERE t1.meuCampo = 0

We are saying that the meuCampo column is in the minhaTabela table via the alias t1 .

If you want to use OR , you must involve the sub-condition in () :

SELECT t1.*, t2.campoXpto                            # selecciona tudo da t1 e campos da t2
FROM minhaTabela t1                                  # identifica minhaTabela como t1
INNER JOIN minhaTabela2 t2 ON (t1.campo = t2.campo)  # match com tabela t2
WHERE t1.meuCampo = 0                                # condição #1
AND t1.meuCampo2 = ''                                # condição #2
AND (t1.meuCampo3 IS NULL OR t1.meuCampo10 IS NULL)  # condição #3

We are saying in condition # 3 that meuCampo3 or meuCampo10 must be NULL .

Your actual query

Your query in the form that you have in the question is confusing and does not clarify where the columns are or how you want to validate the information to collect the records or not.

You should see the database as a person with whom you are having a conversation and ask questions:

  

Database, give me X table records where there are columns that have Y values and have a relation to the J! table

Even after having indented your query, many questions remain:

SELECT * 
FROM tb_detalhe_trabalhador
INNER JOIN tb_trabalhador ON tb_detalhe_trabalhador.id = tb_trabalhador.id
INNER JOIN tb_equipamentos ON tb_detalhe_trabalhador.id = tb_equipamentos.id
WHERE AlvaraNumero = 0
AND (AlvaraValidade='' or AlvaraValidade is Null OR AlvaraValidade='0000-00-00')
AND (AlvaraAnexo='' OR AlvaraAnexo is Null) 
AND AcidenteNumero = 0
AND (AcidenteValidade='' OR AcidenteValidade is Null OR AcidenteValidade='0000-00-00') 
AND (AcidenteAnexo='' OR AcidenteAnexo is Null) 
AND SeguroNumero = 0 
AND (SeguroValidade='' OR SeguroValidade is Null OR SeguroValidade='0000-00-00') 
AND (SeguroAnexo='' or SeguroAnexo is Null) 
AND InstaladorNumero = 0 
AND (InstaladorValidade='' OR InstaladorValidade is Null OR InstaladorValidade='0000-00-00') 
AND (InstaladorAnexo='' OR InstaladorAnexo is Null) 
OR MedicaValidade is NULL 
OR MedicaAnexo is NULL 
OR ProjectistaNumero is NULL 
OR ProjectistaValidade is NULL 
OR ProjectistaAnexo is NULL 
OR GasNumero is NULL 
OR GasValidade is NULL 
OR GasAnexo is NULL 
OR SoldadorNumero is NULL 
OR SoldadorValidade is NULL 
OR SoldadorAnexo is NULL 
OR MecanicoValidade is NULL 
OR MecanicoNumero is NULL 
OR MecanicoAnexo is NULL 
OR ClasSoldadorNumero Is NULL 
OR ClasSoldadorValidade is NULL 
OR ClasSoldadorAnexo is NULL
ORDER BY tb_trabalhador.id 
  • What table the fields belong to:

    When you query more than one table, you should always indicate which table belongs to the column:

    tb_detalhe_trabalhador.meuCampo
    

    or alias t1 to the tb_detalhe_trabalhador table:

    t1.meuCampo
    
  • Type X or Y conditions must always be protected by () to avoid misinterpretation of these conditions:

    The last conditions are OR , but they were () which means that you want records where X and Y and Z or J.
    J will subscribe to X and Y and Z.

    You're basically saying that if one of those columns in OR is NULL , you want to register.

11.03.2014 / 12:35