我相信我在这里试图实现的是嵌套查询,但我不确定如何继续。
我有两个表users和notifications我正在做的是查询notifications表以获取详细信息,但我也在users表上LEFT OUTER JOIN以获取我的用户信息。我有这样的:
USERS TABLE
| user id | username | user image |
NOTIFICATIONS TABLE
| receiver id | sender id |
仅仅获取接收器信息不是问题,因为我使用以下查询:
SELECT
notifications.senderID, notifications.receiverID, users.username, users.image
FROM
notifications
LEFT OUTER JOIN users ON users.id = notifications.receiverID
WHERE notifications.receiverID = ' xxx '
我需要添加的是一种方法,可以为找到的每个记录获取该记录的users.username和users.image(将在users.id上对应notifications.senderID)以及主记录(notification.receiverID 如果可能的话我想把它放在一个查询中,放在PHP foreach循环之外。
使用不同的别名加入users
表两次
SELECT notifications.senderID, notifications.receiverID,
receiver.username as receiver_name, receiver.image as receiver_image,
sender.username as sender_name, sender.image as sender_image
FROM notifications
LEFT OUTER JOIN users as receiver ON receiver.id = notifications.receiverID
LEFT OUTER JOIN users as sender ON sender.id = notifications.senderID
WHERE notifications.receiverID = ' xxx '
这是例如的查询
选择CCD_ 2。id
、User
。username
、User
。password
、User
。email
、User
。role
、User
。created
、User
。modified
、Userdetail
。user_id
、Userdetail
。first_name
、User
0、Userdetail
。address
、Userdetail
。telephone
从cakephp_test
。users
作为User
左联接cakephp_test
。userdetails
作为Userdetail
ON(Userdetail
.user_id
=User
.id
),其中User
。id
=51极限1
在您的情况下是
选择notifications.senderID,notifications.receiverID,users.username,users.image FROM database_name.users AS User LEFT JOIN database_name.notification AS notification ON(users.id=notifications.receiverID)WHERE User.id='xxx'LIMIT 1