MySQL Show rows that exist in one table but not it another

join, mysql

Solution

A simple left join?

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

Records which don't exist on the 'right' side (srp_reseller) will be null.

Problem

I have the following tables: - `organisation` - `organisation_id`, `organisation_name` - `srp_reseller_buffer` - `organisation_id`, `property_id` I want to write a MySQL query which shows me all the records in organisation except those that are in `srp_reseller_buffer` and have a `property_id` of X This is what I have so far: ``` 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 that SQL query is not working. It just shows me a list of all the organisations in organisation table.

Original source