Getting 2 same fields in the same time

Question

I've 3 tables below. (sample)(mySql script)

customer

• cust_id
• cust_name
• user_id
• user_id_2

user

• user_id
• em_id

employee

• em_id
• em_name

How can I call the em_name field for user_id, and em_name field for user_id_2 by crossing user table in the same time with join??

Answer 1

Try this

SELECT  q1.* ,
        q2.em_name AS 'em_name_2'
FROM    (SELECT c.cust_id ,
                c.cust_name ,
                c.user_id ,
                c.user_id_2 ,
                e.em_name
         FROM   dbo.customer AS c
                INNER JOIN dbo.[user] AS u ON c.user_id = u.user_id
                INNER JOIN dbo.employee AS e ON u.em_id = e.em_id
        ) q1
       CROSS JOIN 
       ( SELECT   e.em_id ,
                  e.em_name
         FROM     dbo.customer AS c
                INNER JOIN dbo.[user] AS u ON c.user_id_2 = u.user_id
                INNER JOIN dbo.employee AS e ON u.em_id = e.em_id
    ) q2