check query in 2 tables at the same time with mysql

Asked

Viewed 607 times

4

I have two tables in my BD (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

Performing a query in the table CADASTROS ordering by NOME:

SELECT * FROM CADASTROS ORDER BY NOME ASC

How to perform a survey that returns only the existing records in the table CADASTROS without correspondence with the table FUNCIONARIO?

php mysql sql

3 answers

4


You can do it like 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
  • 1

    Thanks worked 100% here :)

1

To bring all records from the REGISTER 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

  • I tried their way but it didn’t work, the way only Islam worked with me, anyway vlw ;)

  • What is the error you get when executing this select I gave you?

  • did not return any results

  • This means that all ids that are in the registration table are in the employee table.

0

You can develop as follows:

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

This way, you can display the data of the other table too, you can put in the * of From, the fields you want to search... ex: 

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

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

I hope I’ve helped!

Browser other questions tagged php mysql sql

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