Returning multiple columns from SELECT nested in MySQL CASE

Question

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 the name and email address from the corresponding table.

Answer 1

I would use left outer joins in both tables:

 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 

As for the only case, if you need two different columns in your result set, you will need two case clauses.

Answer 2

You cannot use a single case expression to process two separate columns ...

Using:

  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