Join in query .. with if control: is it possible?

I've this db structure:

Message  

msg_by     usertype     message    
2             U         how are you ? 
1             A         i'm fine


SimpleUser 

id          fullname    
1           mike    
2           john


AdminUser    

id           fullname
1            frank     
2            lucas

Ok, i know it's not correct to split users into two table but i've inherited this project.

My question is how to make a query making joining Message with SimpleUser and AdminUser ... with the result to have the correct name for each message based to user开发者_运维问答type: if U the message is from SimpleUser table, if A the message is from AdminUser

Thank you for your help!

---

With a UNION:

SELECT fullname, message FROM msg_by JOIN SimpleUser ON(msg_by = SimpleUser.id)
  WHERE usertype = "U"

UNION ALL

SELECT fullname, message FROM msg_by JOIN AdminUser ON(msg_by = AdminUser.id)
  WHERE usertype = "A"

---

This is one query which will return the rows you want:

SELECT
  msg_by, usertype, message, full_name
FROM
  Message
INNER JOIN
  SimpleUser
ON
  SimpleUser.id = Message.msg_by 
AND 
  Message.usertype = 'U'

UNION ALL

SELECT
  msg_by, usertype, message, full_name
FROM
  Message
INNER JOIN
  AdminUser
ON
  AdminUser.id = Message.msg_by 
AND 
  Message.usertype = 'A'

---

You don't need union. You can add the usertype to the join:

select
  m.*,
  ifnull(a.full_name, s.full_name)
from
  Message m
  left join AdminUser a on a.id = m.msg_by and m.usertype = 'A'
  left join SimpleUser s on s.id = m.msg_by and m.usertype = 'U'

---

Something like that:

SELECT m.message, COALESCE(s.fullname, a.fullname)
FROM Message m
    LEFT OUTER JOIN SimpleUser s
        ON m.usertype = 'U'
        AND m.msg_by = s.id
    LEFT OUTER JOIN AdminUser a
        ON m.usertype = 'A'
        AND m.msg_by = a.id

---

Personally, my natural inclination would be to use the LEFT JOIN approach.

In consideration, however, the UNION approach may be best - if laid out as an inline view instead...

SELECT
  Message.*,
  User.*
FROM
  Message
INNER JOIN
(
  SELECT *, 'A' as UserType From AdminUsers
  UNION ALL
  SELECT *, 'U' AS UserType FROM SimpleUsers
)
  AS User
    ON  User.UserType = Message.UserType
    AND User.ID       = Message.msg_by

Using this inline-view layout may result in better performance, due to only needing to scan the Message table once.

It also directs you towards the option of creating a real view to unify your two tables.