mysql - 我的查询有效，但我正在努力通过合并另一个表来扩展它

Question

如此简单，却又如此困难。我有一个运行良好的查询，但现在我需要进一步扩展它。

目前，查询将用户状态更新连同其他信息一起输出到网站，例如谁写了状态等。

现在，我需要修改代码，以便在该用户被阻止时不输出用户状态(如果他们出现在我的 tbl_blocking 中)

我完全被卡住了，并且在这上面花了最后几个小时。

我要说，
“如果 tbl_blocking.user = :who AND tbl_blocking.blocking != :who，不要输出那个结果”
换句话说，如果登录者 (:who) 屏蔽了某人，则不要显示他们的状态。

    //Get status results
                $sql = "SELECT 

                tbl_status.id as statID, 
                tbl_status.from_user as statFROM, 
                tbl_status.status as statSTATUS, 
                tbl_status.deleted as statDEL, 
                tbl_status.date as statDATE,

                tbl_users.id as usrID, 
                tbl_users.name as usrNAME,
                tbl_users.location as usrLOCATION,
                tbl_users.postcode as usrPOSTCODE,

                tbl_blocking.id as blockID,
                tbl_blocking.user as blockUSER,
                tbl_blocking.blocking as blockBLOCKING,
                tbl_blocking.date as blockDATE,
                tbl_blocking.active as blockACTIVE,

                tbl_photos.profile as photosPROFILE,
                tbl_photos.photo_link as photoLINK,
                tbl_photos.default_photo as photoDEFAULT

                FROM tbl_status 

                LEFT JOIN tbl_users ON tbl_status.from_user = tbl_users.id
LEFT JOIN tbl_blocking ON tbl_status.from_user = tbl_users.id
                LEFT JOIN tbl_photos ON tbl_photos.profile = tbl_users.id 

                WHERE tbl_status.deleted = '0' AND (tbl_photos.default_photo IS NULL OR tbl_photos.default_photo = '1')
    AND (tbl_blocking.user = :who AND tbl_blocking.blocking !=who) OR IS NULL
                ORDER BY tbl_status.date desc

                    ";

Answer 1

实现此目的的一种方法是对 tbl_blocking 使用如下所示的 ON 子句(这似乎是开始的方式):

LEFT JOIN tbl_blocking
  ON tbl_blocking.blocking = tbl_status.from_user
  AND tbl_blocking.user = :who

然后包括以下 WHERE 子句:

 WHERE ....
    AND tbl_blocking.id IS NULL