Left Join Display All Matched and Unmatched 1

[sxschech]

I must be overlooking something simple because I've done this type of query before and it usually works. I'm trying to display all locations regardless of data so that I can identify which items we still need to add to the detail table. For some reason, when I do a left join, it is not showing the missing items. Here is an example:

Note there are more records per location in this table, for space considerations only showing one record per location. Also, these aren't the real table names.

[tt]
TABLE Details
--------------
RunDate WeekNo Location
2/13/2006 21 SF
2/13/2006 21 LA
2/13/2006 21 SD

TABLE Location
--------------
Location (Primary Key)
SF
LA
SD
SJ
SR

--Actual Result--
Location.Location Details.Location
SF SF
LA LA
SD SD

--Needed Result--
Location.Location Details.Location
SF SF
LA LA
SD SD
SJ [red][null][/red]
SR [red][null][/red]
------------------------------------
[/tt]

Here is the query

QUERY Summary
--------------
[tt]SELECT Location.Location, Details.Location
FROM Location LEFT JOIN Details ON Location.Location = Details.Location
WHERE Details.WeekNo=21
GROUP BY Location.Location, Details.Location;[/tt]

When the query is working, I plan to display WeekNo and Sum of Location rather than having a where clause.

 

[PHV]

Your WHERE clause defeats the LEFT JOIN purpose.
SELECT L.Location, D.Location
FROM Location AS L LEFT JOIN (
SELECT Location FROM Details WHERE WeekNo=21
) AS D ON L.Location = D.Location

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

Thanks PHV, that works. Now, how can I get the query result to look like this? (Do I have to create a separate query for each week and union? Hopefully not)

[tt]
WeekNo L.Location D.Location
20 SF SF
20 LA LA
20 SD SD
20 SJ [null]
20 SR [null]
21 SF SF
21 LA LA
21 SD SD
21 SJ [null]
21 SR SR
[/tt]

Or like

[tt]
WeekNo L.Location D.Total
20 SF 10
20 LA 5
20 SD 18
20 SJ [null] or 0
20 SR [null] or 0
21 SF 10
21 LA 6
21 SD 19
21 SJ [null] or 0
21 SR 5
[/tt]

 

[PHV]

You may try either this:
SELECT WL.WeekNo,WL.Location,D.Location
FROM (SELECT DISTINCT WeekNo,L.Location FROM Details,Location AS D) AS WL
LEFT JOIN Details AS D ON WL.WeekNo=D.WeekNo AND WL.Location=D.Location
ORDER BY 1,2
Or this:
SELECT WL.WeekNo,WL.Location,Count(D.Location) AS Total
FROM (SELECT DISTINCT WeekNo,L.Location FROM Details,Location AS D) AS WL
LEFT JOIN Details AS D ON WL.WeekNo=D.WeekNo AND WL.Location=D.Location
GROUP BY WL.WeekNo,WL.Location
ORDER BY 1,2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

Thanks so much, just what I needed. Guess I've barely scratched the surface when it comes to queries like this. Any tips for knowing when to write the query the way you showed versus the simpler method I was trying?