我有一个数据库,我正在努力进行查询。主表people
,我试图做的是仅在client_member_id
匹配时add
选择查询的末尾,favourites
表中的id
。
这个想法是遍历每个People
行,并在行尾添加f.id
,如果该人员 ID 在favourites
表中并且它与client_member_id
的id
匹配。
client_member_id
是提供给每个客户的ID,以便当他们收藏一个人时,详细信息将被放入收藏夹表中。这将是1
或2
.在 SQLFiddle 示例中,client_member_id
2
时FAVE_ID
的预期输出将是68
69
但如果client_member_id
1
则FAVE_ID
两者都是null
我已经把它弄到了似乎重复行的程度,我需要它是不同的。
SELECT *, j.company_id as companyid, j.id as jid, p.id as pid,
(CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN f.id ELSE null END) AS fave_id,
(CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN 1 ELSE 0 END) AS fave
FROM people p
INNER JOIN favourites f
INNER JOIN job j
ON p.job_id = j.id
WHERE p.company_id = 1
ORDER BY p.id ASC
SQLFiddle
提前谢谢你。
您可以使用 LEFT JOIN 来删除不美观的 CASE:
SELECT *,
j.company_id companyid,
j.id jid,
p.id pid,
f.id fave_id,
f.id IS NOT NULL fave
FROM people p
INNER JOIN job j
ON p.job_id = j.id
LEFT JOIN favourites f
ON f.people_id=p.id
AND f.client_member_id=2
WHERE p.company_id = 1
ORDER BY p.id ASC
注意,这将只返回两行你的小提琴,fave_id
和fave
,不是NULL 和 1,但是如果你有一个人没有相应的收藏夹,那么你得到 fave_id
和 fave
、NULL 和 0。
没有重复项,由于fave_id和收藏夹列,您有 4 行。如果要过滤它们,那么这里有一个示例,如何仅显示 fave=1 的值:
SELECT *, j.company_id as companyid, j.id as jid, p.id as pid,
(CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN f.id ELSE null END) AS fave_id,
(CASE WHEN f.client_member_id = 2 AND p.id = f.people_id THEN 1 ELSE 0 END) AS fave
FROM people p
INNER JOIN favourites f
INNER JOIN job j
ON p.job_id = j.id
WHERE p.company_id = 1 AND f.client_member_id = 2 AND p.id = f.people_id
ORDER BY p.id ASC
试试这个SQLFiddle
SELECT *, j.company_id as companyid, j.id as jid, p.id as pid,
(CASE WHEN f.client_member_id = 2 THEN f.id ELSE null END) AS fave_id,
(CASE WHEN f.client_member_id = 2 THEN 1 ELSE 0 END) AS fave
FROM people p
INNER JOIN job j ON p.job_id = j.id
left JOIN favourites f on f.people_id = p.id
WHERE p.company_id = 1
ORDER BY p.id ASC
您没有将收藏夹表联接绑定到任何内容,因此您返回的人数行数 * 收藏夹数。我使用了左联接,因为我猜你不能保证人员表中的每个人都会有一个最喜欢的?否则,如果可以的话,只需更改为内部连接即可。
问候
利亚姆