CODEWITHSUNDEEP
sql
Mustafa Ozbalci
Mustafa Ozbalci

Reputation: 115

How do I view 2 names in one SELECT query

I want to get two user names from users table by matching them with usrID and res_usrID fields from my cases table to view their names from users table with usrGivenName field. I want to view them on same line. How can I do that ?

SELECT 
         caseID
      ,c.date_created
      ,c.date_approval_request
      ,c.date_closed
      ,u.usrGivenName 
      ,c.muh_usrID
  FROM cases AS c
  JOIN users AS u ON c.usrID=u.usrID

Upvotes: 2

Views: 163

Answers (2)

sam
sam

Reputation: 1985

SELECT 
         caseID
      ,c.date_created
      ,c.date_approval_request
      ,c.date_closed
      ,coalesce(u.usrGivenName, ru.usrGivenName, 'Not Available') 
      ,c.muh_usrID
  FROM cases AS c
  left JOIN users AS u ON c.usrID = u.usrID
  left JOIN users AS ru ON c.res_usrID = ru.usrID

Did not test, but you will get the idea.

Upvotes: 1

Parfait
Parfait

Reputation: 107652

Simply use a self join (i.e., additional join to same table):

SELECT 
        c.caseID
      , c.date_created
      , c.date_approval_request
      , c.date_closed
      , u1.usrGivenName as user_name
      , c.muh_usrID
      , u2.usrGivenName as res_user_name

FROM cases AS c
JOIN users AS u1 ON c.usrID = u.usrID
JOIN users AS u2 ON c.res_usrID = u.usrID

Upvotes: 2

Related Questions