我有以下问题,我的大脑燃烧了:(
我有两个具有个人数据的表(一个用于客户'table_a',一个用于有趣的人'table_b'(。在第三个表" table_inv"中,有两个表的条目(a nd b(。
Table_inv具有一个字段" undited_id",其中存储了表A或表B的ID。另一个字段的"类型"具有" cust"或"利息",将其与条目出现的表(a或b(分开。
现在,我必须根据"字段'type'...
的值,我必须从table_a/table_b获取完整数据我尝试了情况,如果没有结果。
我正在寻找这样的东西:
SELECT a.id, a.created, a.userid, a.type, a.inviteid, b.name_first, b.name_last, b.zip, b.city
FROM ext_event_invites a
CASE a.type
WHEN 'CUST' THEN
LEFT JOIN accounts b ON a.inviteid = b.id;
ELSE
LEFT JOIN ext_contacts b ON a.inviteid = b.id;
END CASE
WHERE a.eventid = :eventid AND a.userid = '4711'
ORDER BY b.name_last ASC, b.name_first ASC
有人可以帮我吗?
您可以简单地加入两个表,然后有条件地从相关数据中选择数据。下面给出了另一个选择。我以为'CUST'
的替代方案是'CONT'
(用于'Contact'(,因此您需要调整如果是其他问题...
SELECT
a.id, a.created, a.userid, a.type, a.inviteid,
b.name_first, b.name_last, b.zip, b.city
FROM
ext_event_invites i
LEFT JOIN
(
SELECT
'CUST' as type,
a.name_first,
a.name_last,
a.zip,
a.city
FROM accounts a
UNION ALL
SELECT
'CONT' as type,
c.name_first,
c.name_last,
c.zip,
c.city
FROM ext_contacts c
) as b ON
i.inviteid = b.id AND
i.type = b.type
WHERE
a.eventid = :eventid AND a.userid = '4711'
ORDER BY
b.name_last ASC,
b.name_first ASC
请尝试。这应该起作用。
SELECT a.id, a.created, a.userid, a.type, a.inviteid, b.name_first, b.name_last, b.zip, b.city
FROM ext_event_invites a
IF a.type = 'CUST' THEN
LEFT JOIN accounts b ON a.inviteid = b.id;
ELSE
LEFT JOIN ext_contacts b ON a.inviteid = b.id;
WHERE a.eventid = :eventid AND a.userid = '4711'
ORDER BY b.name_last ASC, b.name_first ASC