check query in 2 tables at the same time with mysql

Navigation
4

I have two tables in my DB (mysql), where:

1st Table: CADASTROS With the following fields: ID, NOME, IDADE, CIDADE

2nd Table: FUNCIONARIO With the following fields: ID, ID_CADASTRO, PROFISSAO, SALARIO

Running a query on the CADASTROS table by ordering NOME : 

SELECT * FROM CADASTROS ORDER BY NOME ASC

How do I perform a search that returns only the existing records in the CADASTROS table that does not match the FUNCIONARIO ? table

    
asked by anonymous 20.05.2016 / 20:39

3 answers

4

You can do this: 

select * from cadastros a
where not exists(select * from funcionario b where a.id = b.id_cadastro)

or so: 

select * from cadastros a
left join funcionario b on b.id_cadastro = a.id
where b.id_cadastro is null
    
20.05.2016 / 21:12
1

To bring all the records of the SIGNATURE table less the IDs that are in the employee table you should write like this: 

SELECT * FROM cadastros where id not in ( select id from funcionario ) ORDER BY nome ASC
    
20.05.2016 / 20:56
0

You can develop as follows: 

SELECT * FROM cadastro as c JOIN funcionario as f ON (c.id=f.id_cadastro)

In this way, you can display the data from the other table too, you can put in the FROm * the fields you want to search for ... ex: 

SELECT c.nome, f.profissao FROM cadastro as c JOIN funcionario as f ON (c.id=f.id_cadastro)

In this case, it will return only the name and profession fields (name of one and profession of the other).

I hope I have helped!

    
20.05.2016 / 20:48
Error reading file in C Touch the photo and open the gallery and camera