MySQL显示一个表中存在但其他表中不存在的行 [英] MySQL Show rows that exist in one table but not it another

问题描述

我有下表:

1. organisation
   • organisation_id，organisation_name

1. organisation
   • organisation_id, organisation_name

• organisation_id，property_id

我想编写一个MySQL查询，向我显示组织中除srp_reseller_buffer中且property_id为X

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 

并且该SQL查询不起作用.它只是显示了组织表中所有组织的列表.

And that SQL query is not working. It just shows me a list of all the organisations in organisation table.

推荐答案

简单的左联接?

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

右侧"(srp_reseller)不存在的记录将为空.

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

这篇关于MySQL显示一个表中存在但其他表中不存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！