Is there a better way to do this?
SELECT subs. * ,
CASE subs.member_type
WHEN 'member' THEN
( SELECT CONCAT_WS( ' ', members.first_name, members.last_name )
FROM members
WHERE members.id = subs.member_id)
ELSE
( SELECT members_anon.username
FROM members_anon
WHERE members_anon.id = subs.member_id)
END AS fullname,
CASE subs.member_type
WHEN 'member' THEN
( SELECT members.email
FROM members
WHERE members.id = subs.member_id)
ELSE
( SELECT members_anon.email
FROM members_anon
WHERE members_anon.id = subs.member_id)
END AS email
FROM subs
WHERE subs.item_id =19
AND subs.item_type = 'blog'
LIMIT 0 , 30
Ideally I would like to have only one CASE
section that returned name and email from the relevant table.
Я бы использовал левые внешние соединения на обоих таблицы:
SELECT subs. * ,
CASE subs.member_type
WHEN 'member' THEN CONCAT_WS( ' ', m.first_name, m.last_name )
ELSE ma.username
END AS fullname,
CASE subs.member_type
WHEN 'member' THEN m.email
ELSE ma.email
END AS email
FROM subs
LEFT OUTER JOIN members m on (m.id = subs.member_id)
LEFT OUTER JOIN members_anon ma on (ma.id = subs.member_id)
WHERE subs.item_id =19
AND subs.item_type = 'blog'
LIMIT 0 , 30
Что касается желания только одного case, если вам нужны два разных столбца в наборе результатов, вам понадобятся два предложения case
.
Вы не можете использовать одно выражение case для обработки двух отдельных столбцов...
Использование:
SELECT s. *,
CASE s.member_type
WHEN 'member' THEN x.fullname
ELSE y.fullname
END AS fullname,
CASE subs.member_type
WHEN 'member' THEN x.email
ELSE y.email
END AS email
FROM SUBS s
LEFT JOIN (SELECT m.id,
CONCAT_WS( ' ', members.first_name, members.last_name ) AS fullname,
m.email
FROM MEMBERS m) x ON x.id = s.member_id
LEFT JOIN (SELECT ma.id,
ma.username,
ma.email
FROM MEMBERS_ANON ma) y ON y.id = s.member_id
WHERE s.item_id = 19
AND s.item_type = 'blog'
LIMIT 0 , 30