CODEWITHSUNDEEP
sqljoin
Rafee
Rafee

Reputation: 4078

get table join with column value

How can i join tables using column value?

I have three tables as listed below:

How can i get the desired output as shown below with SQL query. I.e. Join tables with respect to column values when the following criteria is met:

  1. If user_type = admin then it should join with admin_table.

  2. If user_type = manager then it should join with manager_table.

Desired output:

-----------------------------------------------------
msg_id  | msg_sub   | msg_to_name   | msg_from_name
-----------------------------------------------------
001     | test      | Mandela       | Super Admin
002     | test2     | Mandela       | Kristen

I.e. Get the join sql query based on column value.

EDIT:

I want to fetch the datafrom sql query not form the serverside coding.

I tried this query from here, i.e. Winfred's Idea ( Answered )

However, I could not understand it.

msg_by_usertype is the column based, where the value manager then it should select manager_table and if it is admin the to admin_table

Upvotes: 6

Views: 8892

Answers (3)

rainer
rainer

Reputation: 21

if I understand your question correctly, you want a result like this?

MSG_ID      MSG_BODY    MSG_TO      BY           MSG_BY_USERTYPE 
----------  ----------  ----------  -----------  --------------- 
001         test        adm1        managone     manager         
002         sadff       adm1        adm3?        admin

If so, you could use this

SELECT MSG_ID, MSG_BODY, MSG_TO,                                        
     CASE                                                               
         WHEN MSG_BY_USERTYPE = 'admin' THEN COALESCE(                  
              (SELECT ADMIN_NAME FROM ADMIN_TABLE                       
               WHERE  MSG_BY = ADMIN_ID), RTRIM(MSG_BY) CONCAT '?')     
         WHEN MSG_BY_USERTYPE = 'manager' THEN COALESCE(                
              (SELECT MANAGER_NAME FROM MANAGER_TABLE                   
               WHERE  MSG_BY = MANAGER_ID), RTRIM(MSG_BY) CONCAT '?')   
         ELSE ' '                                                       
     END AS BY,                                                         
 MSG_BY_USERTYPE                                                        
 FROM MESSAGES

Upvotes: 2

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

Please use the below SQL

SELECT msg_id,  
     msg_body,  
     usersBy.userName AS msg_by, 
     usersTo.userName AS msg_to,  
     msg_by_usertype
FROM messages
INNER JOIN 
    (SELECT admin_id As id, admin_name as userName,'admin' as usertype
      FROM admin_table         
     UNION         
     SELECT manager_id As id, manager_name as userName,'manager' as usertype
      FROM manager_table ) usersTo 
      ON msg_to = usersTo.id  and  msg_by_usertype = usersTo.usertype

Upvotes: 3

András Ottó
András Ottó

Reputation: 7695

As far as I understood your question, you can try this:

    SELECT msg_id,  
     msg_body,  
     usersBy.userName AS msg_by, 
     usersTo.userName AS msg_to,  
     msg_by_usertype
    FROM messages
    INNER JOIN 
    (SELECT admin_id As id, admin_name as userName
      FROM admin_table         
     UNION         
     SELECT manager_id As id, manager_name as userName
      FROM manager_table ) usersTo ON msg_to = usersTo.id  

     INNER JOIN 
    (SELECT admin_id As id, admin_name as userName
      FROM admin_table        
     UNION         
     SELECT manager_id As id, manager_name as userName
      FROM manager_table ) usersBy ON msg_by = usersBy.id

Here is an SQL Fiddle to see how it works. (It only works if you cant have an admin who has the same id like a manager. Id should be unique in both tables.)

Upvotes: 5

Related Questions