MYSQL - How to return records that exist in one table and do not exist in another?

Asked

Viewed 162 times

-3

I have the npsparticipantes table that receives the id that comes from the contracts table. How do I recover all contracts that are not added to the npsparticipantes table?

npsparticipantes

contrato

php mysql sql query
  • 2

    Copies your code and not images, so it’s easier for us to look at your problem

2 answers

1


Assuming an example CUSTOMERS & ORDERS. A customer may or may not have orders, but every order belongs to a customer. To find customers without any request:

select clientes.codigo from clientes
     left outer join pedidos 
         on clientes.codigo = pedidos.cliente
     where pedidos.codigo IS null

rationale is that in LEFT JOIN or LEFT OUTER JOIN, all rows of the "left" table appear in the query, but when there are no corresponding rows in the "right" table, the respective columns are "null".

  • +1 I have a strong impression that it is duplicate, qq forma the left Join seems to me the most elegant solution.

0

I didn’t see which reference field between the tables. However, you can do this command. Taking into account that there is a reference key in contract idNPSParticipants.

select * from npsparticipantes 
where idNPSParticipantes not in (select idNPSParticipantes from contrato)

The result would be all npsparticipantes who are not under contract in contract.

If I want to go deeper with NOT IN and IN, see here link

Browser other questions tagged php mysql sql query

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