Mysql Join A table with with two other tables based on a flag column

I have a table which contains messages from both users and organizations. The table contains a field called usertype which indicates whether the message was sent by a user or an organization.

I have to join this table with two tables containing user details and organization details respectively.

How can i write a join query with thes 2 tables?

I think i can achieve this via a union, but can it be done using only joins?

Table - USER fetch开发者_如何学运维 : user name

Table - Organization fetch : organization name

Table Messages - entries userid , usertype. have to get username by checking datatype

UNIONs and JOINs do very different things. UNIONs append records onto the end of a query, JOINs add fields to the end of a record.

If your UNION query works, then I'd recommend you stick with it. If you really want to do this with JOINs, then you can do the following and alter your workflow accordingly.

SELECT message_id, message_title, user.name, corp.name
FROM group_messages
LEFT JOIN users ON
   users.user_id = group_messages.user_id
   AND group_messages.usertype = "1"
LEFT JOIN corp ON
   corp.user_id = group_messages.user_id
   AND group_messages.usertype = "0"
;

What this will do is return records with fields for user AND fields for corp. When the usertype is 1 the user fields will be populated and the corp fields will be null. When the usertype is 0 the corp fields will be populated and the user fields will be null. You'll need to write application logic to read the returned usertype and determine which set of fields to read.

Honestly though, if you do something like that you're a lot better off just making it two separate queries.

If you don't want the data for each type to be in separate fields, you shouldn't use a JOIN. If you want the data from each type to be returned in the same field, you want a UNION which you already have.

My Query using Union can it be implemented using join ?

SELECT message_id, CONCAT_WS(' ',fu.user_first_name,fu.user_last_name) uname, user_type, message_title,message_content, posted_on FROM group_messages fgm JOIN users fu ON fgm.user_type='1' AND fu.user_id=fgm.user_id WHERE group_id='1' UNION
SELECT message_id, corporate_name uname, user_type, message_title, message_content, posted_on FROM group_messages fgm JOIN corporate fc ON fgm.user_type='0' AND fc.corporate_id=fgm.user_id WHERE group_id='1'