Ibrahim Hasanov Ibrahim Hasanov - 1 month ago 6
SQL Question

How to achiave this:MYSQL SELECT if true select a else select b?

I want to select user.* if chat.chat_type IS NULL,but if chat.chat_type =1
I want to select group.*

SELECT

CASE
WHEN chat.chat_type IS NULL THEN user.*
WHEN chat.chat_type =1 THEN group.*
END
,x,y FROM `chat` LEFT JOIN group ...


How I can achieve this?

Answer

The database design as described is not so good, because you have an ID in your chat table that can either be a user ID or a group ID. Thus you cannot have a constraint (foreign key) on this column, which makes it possible to put any value in there, i.e. even an ID that doesn't exist.

Anyway, with the design given, to get either user or group you outer join both tables on the conditions you already mentioned. Then use COALESCE to see whether you got a user or a group in the result row.

select c.from, coalesce(u.name, g.name) as to_name
from chat c
left join usr u on c.chat_type is null and c.to = u.user_id
left join grp g on c.chat_type = 1 and c.to = g.group_id
where c.chat_type is null or c.chat_type = 1;