6
votes

Obtenir la table Joindre avec la valeur de la colonne

Comment puis-je rejoindre des tables à l'aide de la valeur de colonne?

J'ai trois tables comme indiqué ci-dessous: p>

messages_table strong> p>

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

admin_table strong> p>
--------------------------- manager_id | manager_name --------------------------- 88 | Mandela 94 | Kristen

manager_table strong> p>
-------------------------â€” admin_id | admin_name -------------------------â€” 001 | Super Admin

sql join

3 commentaires

Quelles sont les colonnes que vous voulez récupérer?

De plus, comment les tables sont-elles liées les unes aux autres? Exemple de la table des messages et de la table d'administration? Quelle est leur colonne de liaison

J'ai édité la question, s'il vous plaît jeter un oeil.

3 Réponses :

5
votes

Autant que j'ai compris votre question, vous pouvez essayer ceci:

    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

0 commentaires

3
votes

Veuillez utiliser le ci-dessous 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

0 commentaires

2
votes

Si je comprends votre question correctement, vous voulez un résultat comme celui-ci? XXX PRE>

Si tel est le cas, vous pouvez utiliser ce P>

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

0 commentaires