mysql排除两个表中具有相同id的结果

Question

我有一个有效的查询，可以获取一些结果。

    $sql = "
        SELECT 
            swdl.wedding_dress AS wedding_dress,
            wd.name AS name,
            wdi.url AS image
        FROM salons_wedding_dresses_link AS swdl 
            LEFT JOIN wedding_dresses AS wd ON swdl.wedding_dress = wd.id
            LEFT JOIN wedding_dress_images AS wdi ON wdi.wedding_dress = wd.id AND wdi.main_image = 1    
            WHERE swdl.salon = ? AND wd.active = 1
    ";

我想要的是将其更新为仅获取表 salons_wedding_dresses_link 中的 wedding_dress 不在表 wedding_dress_dress_collection_link 列 中的结果>wedding_dress_id 但我没有这样做。 下面是更新后的查询，更新内容以粗体显示。

            $sql = "
        SELECT 
            swdl.wedding_dress AS wedding_dress,
            wd.name AS name,
            wdi.url AS image
        FROM salons_wedding_dresses_link AS swdl 


LEFT JOIN wedding_dress_dress_collection_link AS wddcl
------------------------------------------------------

            LEFT JOIN wedding_dresses AS wd ON swdl.wedding_dress = wd.id
            LEFT JOIN wedding_dress_images AS wdi ON wdi.wedding_dress = wd.id AND wdi.main_image = 1    
            WHERE 

swdl.wedding_dress NOT IN (SELECT wddcl.wedding_dress_id FROM wddcl) AND
------------------------------------------------------------------------



swdl.salon = ? AND wd.active = 1
        ";

Answer 1

选择查询以获取salons_wedding_dresses_link中存在但不存在于wedding_dress_dress_collection_link中的结果:

 select s1.*,s2.wedding_dress_collection_id,s2.wedding_dress_id  
 from salons_wedding_dresses_link s1
 left join wedding_dress_dress_collection_link s2 on
 s1.wedding_dress=s2.wedding_dress_id 
 whrere s2.wedding_dress_id  is null;