Complex connection request

Question

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 refers 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 make a connection request that basically selects all the messages for a specific person, but the fact is that message_to.user_id can either refer to the person facebook ID or the person ID (a reference to user.id column).

So basically the query should work as follows

• he should select all the messages in the message table, and if messages.type = 'facebook' checks if message_to.user_id is equal to the FACEBOOK ID. (note that messages_to table stores recipients for each message identifier)
• BUT if messages.type = 'main' checks to see if messages_to.user_id matches person USER ID (USER.id)

Is it possible to efficiently use mysql join query ?

Table

messages_tos stores ALL recipients for each message in the MESSAGES table. There may be MORE THAN ONE RECIPIENT FOR A MESSAGE.

Answer 1

I assume this is a request.

 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') 

Answer 2

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 are your tables messages and messages_to separated and do not use a single table for this? (I assume a ratio of 1: 1)