Multiple joins into the same table (TSQL)

Question

I'm needing rows from the Employee table that have null values in OwnerID field and GMID field.

    SELECT b.FirstName + space(1) + b.LastName AS OwnerName,
           c.FirstName + space(1) + c.LastName AS GeneralManager
     FROM Store a 
        JOIN Employee b ON a.OwnerID = b.EmployeeID
        JOIN Employee c ON a.GMID = c.EmployeeID

The query is working...but the store table has null values in the OwnerID and the GMID fields. What changes do I need to bring back the null rows also?

Update This is the corrected query.

   SELECT b.FirstName + space(1) + b.LastName AS OwnerName,
          c.FirstName + space(1) + c.LastName AS GeneralManager  
   FROM Store a 
     LEFT JOIN Employee b ON a.OwnerID = b.EmployeeID
     LEFT JOIN Employee c ON a.GMID = c.EmployeeID

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017

score 2 · Accepted Answer · answered Jan 27 '18 at 00:44

Use left join instead of join.

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Type of joins: See more here

Multiple joins into the same table (TSQL)

1 Answers1