Outer Join not Returning all Values from the "All" Table

Rex
Rex used Ask the Experts™
on
I have a query where I am trying to extract values from a table for every date in a given time period. If no entries were maded for a given date, there is no entry in the RIGHT Table. I want to include those nulls as well (as zeroes)

to do that, I have attempted to create an Outer Join where the data table is Left Joined with a table of all possible dates . To my understanding, if there is no corresponding record in the Right Table for a date in the Left Table, the Left Outer Join should return a null for that date.

Instead, it is acting as a regular Select query. I am only returning values for data that exists in the Right table. Below is my SQL. Can somebody please tell me what I am doing wrong? Thanks.
SELECT KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")" AS Defect INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table
FROM PossibleCreateDates LEFT JOIN KO_QN_Data ON PossibleCreateDates.PossCreateDates = KO_QN_Data.[Created on]
WHERE (((KO_QN_Data.[Short text for code])="Kimball Office Furniture") AND ((GetNOFGSA([Product Hierarchy]))="Not NOF GSA") AND ((KO_QN_Data.Field20)="White Residue"))
GROUP BY KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")", KO_QN_Data.Field20;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The problem is that the WHERE clause filters out those rows. To correct this you should move all filters into the 'ON' clause, which respects the LEFT JOIN.

Try the following:
SELECT KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")" AS Defect INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table


FROM PossibleCreateDates 
LEFT JOIN KO_QN_Data ON PossibleCreateDates.PossCreateDates = KO_QN_Data.[Created on]
AND (((KO_QN_Data.[Short text for code])="Kimball Office Furniture") 
AND ((GetNOFGSA([Product Hierarchy]))="Not NOF GSA") 
AND ((KO_QN_Data.Field20)="White Residue"))

GROUP BY KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")", KO_QN_Data.Field20;

Open in new window

RexQuality Leader

Author

Commented:
I pasted what you had in directly. I received a "JOIN expression not supported" error when trying to save it though. It won't let me save it.

I am using Access (2007)

Commented:
Hmm,, I think it might be to do with all the parenthesis, could you try re-jigging them or removing them?
Sorry I can't test it without having the same database but the logic of the query is sound...

Try the below and see you get better results!
SELECT KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")" AS Defect INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table


FROM PossibleCreateDates 
LEFT JOIN KO_QN_Data 
ON PossibleCreateDates.PossCreateDates = KO_QN_Data.[Created on]
AND KO_QN_Data.[Short text for code])="Kimball Office Furniture"
AND GetNOFGSA([Product Hierarchy]))="Not NOF GSA"
AND KO_QN_Data.Field20)="White Residue"

GROUP BY KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")", KO_QN_Data.Field20;

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
Alternatively it may be that Access doesn't support this kind of condition in the ON clause.... I primarily work with MS SQL.
RexQuality Leader

Author

Commented:
Same error. It is hanging up on the first AND statemenet after the Join. I think you may be right about the Access limitation, but I don't know for sure. I have never seen anything like that in the Help articles on Joins. It's always just two tables.
RexQuality Leader

Author

Commented:
My problem is that there are 100 or so defect types in the field called Field20 (I didn't name it!)

I need a breakdown of each, including the nulls. If I don't filter for the specific defect, there will always be at least one of the other defects present, so it is would not be a null for that date.

Doing it that way, in other words, I get different column lengths for the various defects.

I am using Access to create a table for Minitab to analyze.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You are going to have to do this with a subquery.  Syntax similar to:

SELECT TableA.*, QryB.*
FROM Table A
LEFT JOIN (SELECT TableB.* FROM TableB WHERE TableB.SomeField = SomeValue) as QryB
ON TableA.ID = TableB.ID

When Access compiles this it will change the ( ) around the subquery to [ ] followed by a dot, so editing it will become problematic.  The best way to avoid that is just to leave it in SQL view, don't go back to the design view if you can help it.

HTH
Dale
RexQuality Leader

Author

Commented:
Ooh. I always have trouble with subqueries. (Although I suspect you are correct on this being the appropriate vehicle) It will take me a while to work through translating this. Thanks. I will apprise you of my results.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Looking at your original query, I'm puzzled as to why you are even includingthe PossibleCreateDates table, since nothing from that tables appears in the query.  A quick assessment of the query would indicate:

SELECT PCD..PossCreateDates , Q.[Created On], Q.Defect
INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table
FROM PossibleCreateDates as PCD
LEFT JOIN
(SELECT KO_QN_Data.[Created on],
               KO_QN_Data.Field20 & " (" & KO_QN_Data.[Code group] & ")" AS Defect
  FROM KO_QN_Data
  WHERE KO_QN_Data.[Short text for code]="Kimball Office Furniture"
  AND GetNOFGSA([Product Hierarchy])="Not NOF GSA"
  AND KO_QN_Data.Field20="White Residue") as Q
ON PCD.PossCreateDates = Q.[Created on]

I don't see any reason for the Group By clause, as you were not doing any counts, unless you need to add some sort of Count(*) in the subquery. In which case the Group By clause needs to go immediately after the "White Residue" portion of the subqueries WHERE clause.

HTH
Dale
RexQuality Leader

Author

Commented:
Yes. There is data field that includes QTY that I need to unclude in the subquery.

The Table PossibleCreateDates is a table of all dates. The table is PossibleCreateDates and the only Field in it is PossCreateDates. I need it to capture dates for which no data was recorded (0 events) as well as the dates where data was recorded. Thus, the Outer Join.

The code you provided above worked. (Thank you!) However, for some reason, it is putting an empty pair of parentheses () in the Defect field when there is no "White Residue" in that Field. Is there a way to just have that be blank (a null)

With respect to the Group By, you are correct. I forgot a field I need called [DefectQty (ext)]

It contains numeric values, which I would like to Sum, and Group by Date.

Based on what you said, I am thinking it would look like this?


SELECT PossibleCreateDates.PossCreateDates , Q.[Created On], Q.Defect, Q.[DefectQty (ext)]
INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table 
FROM PossibleCreateDates as PCD
LEFT JOIN
(SELECT KO_QN_Data.[Created on], 
               KO_QN_Data.Field20 & " (" & KO_QN_Data.[Code group] & ")" AS Defect, KO_QN_Data.[DefectQty (ext)] AS QN_Total
  FROM KO_QN_Data 
  WHERE KO_QN_Data.[Short text for code]="Kimball Office Furniture"
  AND GetNOFGSA([Product Hierarchy])="Not NOF GSA"
  AND KO_QN_Data.Field20="White Residue") GROUP BY PossibleCreateDates.PossCreateDates as Q 
ON PCD.PossCreateDates = Q.[Created on]

Open in new window

RexQuality Leader

Author

Commented:
that is giving me a Syntax error in the last FROM Clause
RexQuality Leader

Author

Commented:
I'm sorry, it is hanging up on GROUP. I obviously have it in the wrong place
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
OK,

You have a couple of issues

1. In order to Sum the QTY field, you need to do that inside the subquery, and the Group By clause is associated with the subquery as well, so all of that has to go inside the ( ).

2.  The next issue as to do with your concatenated DEFECT column, which is based on:

KO_QN_Data.Field20 & " (" & KO_QN_Data.[Code group] & ")" AS Defect

Whenever you use the ampersand "&" to concatenate two strings together and one of them is null, the "sum" will be the one that is not NULL, so:  "(" & NULL = "(".  To get rid of this, you can use the "+" to concatenate strings.  Using the plus operator, if either value is NULL, then the "sum" will be null also.  So, if what you want if the [Code Group] field is null is just what is in Field20, without the "()" then you need to change that line to read as below, and I would wrap the entire concatenation in ( )

(KO_QN_Data.Field20 & " (" + KO_QN_Data.[Code group] + ")") AS Defect

So, I think the final query should look like (you really don't need Q.[Created On] in the select clause because it will either be the same as PossCreateDates, or NULL

SELECT PossibleCreateDates.PossCreateDates ,
             Q.Defect,
             Q.QN_Total
INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table  
FROM PossibleCreateDates as PCD
LEFT JOIN
(SELECT KO_QN_Data.[Created on],  
               (KO_QN_Data.Field20 & " (" + KO_QN_Data.[Code group] + ")") as Defect,
               SUM(KO_QN_Data.[DefectQty (ext)]) AS QN_Total
  FROM KO_QN_Data  
  WHERE KO_QN_Data.[Short text for code]="Kimball Office Furniture"
  AND GetNOFGSA([Product Hierarchy])="Not NOF GSA"
  AND KO_QN_Data.Field20="White Residue")
  GROUP BY KO_QN_Data.[Created on],
               (KO_QN_Data.Field20 & " (" + KO_QN_Data.[Code group] + ")") as Defect
) as Q
ON PCD.PossCreateDates = Q.[Created on]
RexQuality Leader

Author

Commented:
I am still getting "Syntax Errors in FROM Clause" when trying to save. When I paste the above code in, I get the Syntax error below. When I click OK on that, it shows that it is highlighting GROUP.
Syntax-Error-in-FROM-Clause.jpg
Syntax-Error-Hanging-on-GROUP.jpg
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Sorry, my bad.

Remove the ) at the end of the line preceeding the GROUP

Dale
RexQuality Leader

Author

Commented:
Closer! I got past the Save, but got a weird "Can't find" error when executing the query.
Can-t-Find-Error.jpg
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
OK, seems like a copy paste error.

Try pasting the following into a new query and see what you get.  If that works, drop the "as Defect" from the line preceeding:

) as Q

SELECT KO_QN_Data.[Created on],  
       (KO_QN_Data.Field20 & " (" + KO_QN_Data.[Code group] + ")") as Defect, 
       SUM(KO_QN_Data.[DefectQty (ext)]) AS QN_Total 
  FROM KO_QN_Data  
  WHERE KO_QN_Data.[Short text for code]="Kimball Office Furniture" 
  AND GetNOFGSA([Product Hierarchy])="Not NOF GSA" 
  AND KO_QN_Data.Field20="White Residue"
  GROUP BY KO_QN_Data.[Created on],
     (KO_QN_Data.Field20 & " (" + KO_QN_Data.[Code group] + ")")

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
BTW, that was my copy/paste error, not yours.  ;-)
RexQuality Leader

Author

Commented:
OK. The first worked, so I dropped the "as Defect" from the line you stated. The query executed, but gave me the paramter prompt below. I verified that table exists, that is the correct field name, and there is data in it. Don't undertand the prompt...

Needless to say, without those dates, the table generated by the query was weird
Parameter-Prompt.jpg
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Again my fault.  That's the problem with writing air code, you have no way to test it.

Because I used an alias for the table [PossibleCreateDates], I should have used that alias in the SELECT statement.  The main SELECT statement should read:

SELECT PCD.PossCreateDates , Q.Defect, Q.QN_Total

Dale
RexQuality Leader

Author

Commented:
Wow. Fantastic. That works with the exception of retruning the nulls as zeroes. I feel like you've put so much work into this, and I really appreciate it, would you like me to post that as a related question? specifically, it is returning blanks (nulls) for dates from the PCD table when there was no data, which is what I needed it to do.

However, for Minitab to analyze it properly, I am going to need to convert those nulls to zeroes before I set up Minitab to ODCB this table into minitab.

Would you like me to post that as a spearate, related question?

Rex
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
No need to post separte question.

Use the NZ( ) function to convert NULLs to a zero or text, as an example:

SELECT PCD.PossCreateDates ,
              NZ(Q.Defect, "No defects noted") as Defect,
              NZ(Q.QN_Total, 0) as QN_Total

RexQuality Leader

Author

Commented:
I used NZ(Q.QN_Total, 0) as QN_Total in the Select statement and it worked. However, the table lists the field as data type "Text"

I am afraid of Data Type mismatches. Can I make sure it goes into the table as a numeric data field?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Although NZ is supposed to return a variant, it frequently returns a string.

Try cINT(NZ(Q.QN_Total, 0)) as QN_Total

RexQuality Leader

Author

Commented:
That did it! Fantastic. And Thank You very much for your time and effort. I really appreciate it.

Rex
RexQuality Leader

Author

Commented:
That did it! Fantastic. And Thank You very much for your time and effort. I really appreciate it.

Rex
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial