Databases Querying Relational Databases Subqueries Using a Subquery to Create a Temporary Table (Part 2)

Sharina Jones
PRO
Sharina Jones
Pro Student 18,771 Points

Query producing incorrect results

I've entered the following query, but the results I get don't match the video. I can't figure out where I've gone wrong. Any help would be greatly appreciated.

--combined as subqueries
SELECT sr.LastName, Loc1.StLouisAmount, Loc2.ColumbiaAmount FROM SalesRep AS sr
  LEFT OUTER JOIN(
    SELECT SalesRepID, SUM(SaleAmount) AS StLouisAmount
    FROM Sale AS s WHERE s.LocationID = 1
    GROUP BY SalesRepID
  ) AS Loc1 ON sr.SalesRepID = Loc1.SalesRepID
  LEFT OUTER JOIN(
    SELECT SalesRepID, SUM(SaleAmount) AS ColumbiaAmount
    FROM Sale AS s WHERE s.LocationID = 2
    GROUP BY SalesRepID
  ) AS Loc2 ON sr.SalesRepID = Loc1.SalesRepID;

Thanks.

1 Answer

Emmanuel C
Emmanuel C
7,630 Points

Hey Sharina,

The last line

 AS Loc2 ON sr.SalesRepID = Loc1.SalesRepID;

I believe should be

 AS Loc2 ON sr.SalesRepID = Loc2.SalesRepID;

Different temp table.