mysql - JOIN - 显示缺失的行但将它们附加到特定列

Question

两个表

MEDIA_APPROVALS
[media_id] [firm_id] [approval_status]

FIRM_LIST
[firm_id] [firm_code]

最终目标是显示哪些媒体未得到哪些公司的批准。

默认情况下，MEDIA_APPROVALS 表中列出的所有介质均未得到 FIRM_LIST 表中列出的公司的批准。因此，为了使媒体获得批准，该行必须包含带有approval_status=1的firm_id。

如果媒体片段位于 MEDIA_APPROVALS 表中，并且该行包含事务所 ID 且approval_status=0，则很简单 - 它未获得批准。

对我来说比较棘手的地方是:如果 MEDIA_APPROVALS 表中存在针对 media_id/firm_id 连接的 MISSING ROW，则该 media_id 未获得该公司的批准。

最终我想达到这个目的:

MEDIA_APPROVALS
100   1   1
100   2   1
101   1   0
101   2   0
101   3   1
102   1   1

FIRM_LIST
1   AA
2   BB
3   CC

QUERY OUTPUT
100   CC   0
101   AA   0
101   BB   0
102   BB   0
102   CC   0

我是一名 PHP/Web 程序员，而不是数据库管理员。非常感谢您的帮助!

Answer 1

那么您需要媒体和公司的所有可能组合？哦，那是交叉连接。我们不经常这样做:

WITH MediaFirms As (
    SELECT m_id.media_id, f_id.firm_id
    FROM (SELECT DISTINCT media_id FROM MEDIA_APPROVALS) m_id
    CROSS JOIN (SELECT DISTINCT firm_ID FROM FIRM_LIST) f_id
)
SELECT mf.media_id, mf.firm_id, coalesce(ma.approval_status, 0) "approval_status"
FROM MediaFirms mf
LEFT JOIN MEDIA_APPROVALS ma ON ma.media_id = mf.media_id AND ma.firm_id = mf.firm_id
WHERE coalesce(ma.approval_status,0) <> 1;