Geek asks and answers

MySQL Show rows that exist in one table but not

I have the following tables:

  • organisation
    • organisation_id, organisation_name
  • srp_reseller_buffer
    • organisation_id, property_id

I want to write a MySQL query that shows me all the records in the organization, except those that are in srp_reseller_bufferand have property_idfor X

This is what I still have:

SELECT * FROM organisation
    WHERE NOT EXISTS (
        SELECT * FROM organisation 
            LEFT OUTER JOIN srp_reseller_buffer 
                ON srp_reseller_buffer.organisation_id = organisation.organisation_id 
            WHERE srp_reseller_buffer.property_id is NULL

And this SQL query is not working. It just shows me a list of all the organizations in the organization table.

+3
source share
2 answers

Simple left dick?

SELECT organisation.*
FROM organisation
LEFT JOIN srp_reseller_buffer ON
    (organisation.organisation_id = srp_reseller_buffer.organisation.id AND property_id = 'X')
WHERE srp_reseller_buffer.organisation_id IS NULL

Entries that do not exist on the right side (srp_reseller) will be empty.

+4
source

You need to include the condition from the external statement SELECTin the internal.

Just look at the internal statement SELECT:

SELECT * FROM organisation 
    LEFT OUTER JOIN srp_reseller_buffer 
        ON srp_reseller_buffer.organisation_id = organisation.organisation_id 
   WHERE srp_reseller_buffer.property_id is NULL

, , .

, SELECT, organisation. :

SELECT * FROM organisation
    WHERE NOT EXISTS (<the result from the inner query>)

<the result from the inner query> organisation ( not ), organisation.

- :

SELECT * FROM organisation o
    WHERE NOT EXISTS (
        SELECT * FROM srp_reseller_buffer
            WHERE srp_reseller_buffer.organisation_id = o.organisation_id
              AND srp_reseller_buffer.property_id = X
    )
0

More articles:


All Articles