CODEWITHSUNDEEP
phpmysqlmysql5
Chris Hansen
Chris Hansen

Reputation: 8685

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

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.

Upvotes: 6

Views: 154

Answers (2)

knittl
knittl

Reputation: 265956

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)

Upvotes: 1

ace
ace

Reputation: 7603

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

Upvotes: 2

Related Questions