我有一个充满用户状态更新的数据库表。我想输出所有这些结果,前提是发布该状态的用户未被阻止。
WHERE 子句正在满足......但出于某种原因,它也决定只输出"用户 ID"存在于tbl_blocking列之一中的状态更新。
显然,如果该用户在任何时候都没有出现在阻止表中(从未被阻止或阻止任何人),则仍应显示其状态。
我怎样才能克服这个问题?我哪里出错了?
//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
FROM tbl_status
INNER JOIN tbl_users ON tbl_status.from_user = tbl_users.id
INNER JOIN tbl_blocking ON tbl_users.id = tbl_blocking.user
WHERE
tbl_status.deleted = '0'
AND tbl_blocking.active = '0'
AND tbl_blocking.user != :who
AND tbl_blocking.blocking != :who
ORDER BY
tbl_status.date desc
LIMIT 200
";
如果要获取连接表中不匹配的行,则必须使用 LEFT JOIN
。然后,如果您希望能够在连接表中选择特定行,则必须包含特定的NULL
测试以保留该行。
$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
FROM tbl_status
INNER JOIN tbl_users ON tbl_status.from_user = tbl_users.id
LEFT JOIN tbl_blocking ON tbl_users.id = tbl_blocking.user
WHERE
tbl_status.deleted = '0'
AND (tbl_blocking.user IS NULL
OR (tbl_blocking.active = '0'
AND tbl_blocking.user != :who
AND tbl_blocking.blocking != :who))
ORDER BY
tbl_status.date desc
LIMIT 200
";
问题是您正在连接阻塞表。这意味着仅显示阻止表中的用户和用户表。你想离开加入tbl_blocking和tbl_blocking2看起来。我不确定您的架构,以及为什么在同一张桌子上加入两次。
例如:
User join Blocking
1,2,3 2,3
will return ids (assuming select user.id, blocking.id):
2,2
3,3
This will OMIT user 1 as blocking does not contain id 1.
User left join blocking
1,2,3 2,3
will return
1, null (this will be null since blocking does not contain an id of 1)
2, 2
3, 3
This will include id 1 that you are missing.
这里有一些东西可以更好地解释加入:加入
你的 mysql 摘录:
FROM tbl_status
INNER JOIN tbl_users ON tbl_status.from_user = tbl_users.id
LEFT JOIN tbl_blocking ON tbl_users.id = tbl_blocking.user
由于状态和用户似乎是 1 对 1 记录,因此连接应该没问题。阻止用户的左联接是必需的。这将提供用户表的所有记录,无论阻塞表中的内容如何。我提供了一个资源来查看,这将进一步解释它。如果对您有所帮助,我也可以在这里开始聊天。