How to make a selection from the database by joining with different tables depending on the value of the field?

Question

MySQL

there is a table that stores: user id, message id and type message type, depending on the message type, you need to connect the table with another. let's say if the type is "user" then join with the Users table if 'System' then with the Systems table
something like:
SELECT *
FROM tbl_notice
IF (type="user") { JOIN tbl_users ....}
else{JOIN tbl_system.. .}
WHERE id_user=1

Answer 1

Fields (kolvo and on type) in both tables are identical?
If yes, and splitting the tables is still necessary, then two queries and a union result

Answer 2

I think the essence will be clear - we join the tables with a left join and if the value in the first case is NULL, then we take another value. If there will be more than 2 message types, use the coalesce operator

select 
[ id пользователя]
 ISNULL(U.ID, system.id] as [ID сообщения]
 from tbl_notice  N
left join user  U ON  U.ID=N.[id сообщения] and type="user"
left join Systems  S ON  S.ID=N.[id сообщения] and type="System"

PS Late I saw that the question is for MYSQL, but for MS SQL it will work for sure.

Answer 3

Here