complex join query

I have MYSQL tables as follows

user TABLE {id INT}

profile TABLE {user_id INT, facebook_id varchar(50)}

messages TABLE {id INT, message TEXT, from_id INT, type enum('main','facebook'}

messages_to TABLE {user_id varchar(50), message_id INT}

profile.user_id REFERS To user.id
- the messages_to.message_id refers to the messages.ID column.
- the messages_to.user_id r开发者_如何学Pythonefers to profile.user_id IF messages.type = 'main' BUT
  if message_type = 'facebook' THEN messages_to.user_id REFERS to profile.facebook_id

I want to do a join query that basically selects all the messages to a specific person, but the thing is the messages_to.user_id can refer to either the person's facebook ID or the person's ID (a reference to user.id column).

So basically the query should work as follows

• it should select all the messages in messages table, and if messages.type = 'facebook' checks if messages_to.user_id equals the person's FACEBOOK ID. (note that messages_to table stores the recipients for each messages ID)
• BUT if the messages.type = 'main' checks if the messages_to.user_id equals the person's USER ID (USER.id)

Is it possible to do a mysql join query for that efficiently?

messages_tos table stores ALL the recipients for each message in the MESSAGES table. THERE CAN be MORE THAN ONE RECIPIENT for a message.

---

I guess this is the query.

SELECT messages.*,profile.* 
FROM messages 
JOIN messages_to ON messages.id = messages_to.message_id
JOIN profile ON 
  (profile.user_id = messages_to.user_id AND messages.type = 'main') 
  OR (profile.facebook_id = messages_to.user_id AND messages.type = 'facebook')  

---

something like that?

select m.*, u.*
from messages m
inner join messages_to mt
on m.id = mt.message_id
left join profile p
on    ( m.type = 'facebook' and mt.user_id = p.facebook_id)
   or ( m.type = 'main' and mt.user_id = p.user_id)
left join users u
on p.user_id = u.id

why do you have your messages and messages_to tables split up and do not use a single table for this? (i'm assuming an 1:1 relation)