Can you explain these query results?

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

SELECT Q.*, D.TargetDate AS GRRCHTarg, D.ActualDate AS GRRCHAct
FROM (qryRpt5YrProgress4 AS Q LEFT JOIN tblPkgDates AS D ON
Q.[5YrPkgID]=D.PkgID) LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID = DT.ID
WHERE DT.DateType = 'GRRC Hearing';

qryRpt5YrProgress4 returns 1907 rows, yet the above query returns only 1645
rows. I thought a left join was supposed to return all the rows in the
leftmost table? I need to get all the rows in qryRpt5YrProgress4 with the
results of the other joins in the 2 new columns.
 
Try dropping LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID = DT.ID and
see what you get.
 
Sure, but a WHERE clause is applied AFTER the join and, in your case, it
removes any rows that DT cannot matched and was filled with a NULL by the
result of the join. So, try:

WHERE DT.DateType = 'GRRC Hearing' OR DT.DateType IS NULL


Not that we test DT.DateType in the table itself, no, but what would appear
under DT.DateType AFTER the execution OF THE JOIN.




Hoping it may help,
Vanderghast, Access MVP
 
If I remove the second left join and where clause, I get 10550 rows,
completely not what I want.

KARL DEWEY said:
Try dropping LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID = DT.ID and
see what you get.
--
KARL DEWEY
Build a little - Test a little


mscertified said:
SELECT Q.*, D.TargetDate AS GRRCHTarg, D.ActualDate AS GRRCHAct
FROM (qryRpt5YrProgress4 AS Q LEFT JOIN tblPkgDates AS D ON
Q.[5YrPkgID]=D.PkgID) LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID = DT.ID
WHERE DT.DateType = 'GRRC Hearing';

qryRpt5YrProgress4 returns 1907 rows, yet the above query returns only 1645
rows. I thought a left join was supposed to return all the rows in the
leftmost table? I need to get all the rows in qryRpt5YrProgress4 with the
results of the other joins in the 2 new columns.
Click to expand...
Click to expand...
 
Thanks for the response.
I thought you might have the answer but I still get only 1645 rows with your
modification.


Michel Walsh said:
Sure, but a WHERE clause is applied AFTER the join and, in your case, it
removes any rows that DT cannot matched and was filled with a NULL by the
result of the join. So, try:

WHERE DT.DateType = 'GRRC Hearing' OR DT.DateType IS NULL


Not that we test DT.DateType in the table itself, no, but what would appear
under DT.DateType AFTER the execution OF THE JOIN.




Hoping it may help,
Vanderghast, Access MVP



mscertified said:
SELECT Q.*, D.TargetDate AS GRRCHTarg, D.ActualDate AS GRRCHAct
FROM (qryRpt5YrProgress4 AS Q LEFT JOIN tblPkgDates AS D ON
Q.[5YrPkgID]=D.PkgID) LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID =
DT.ID
WHERE DT.DateType = 'GRRC Hearing';

qryRpt5YrProgress4 returns 1907 rows, yet the above query returns only
1645
rows. I thought a left join was supposed to return all the rows in the
leftmost table? I need to get all the rows in qryRpt5YrProgress4 with the
results of the other joins in the 2 new columns.
Click to expand...
Click to expand...
 
This did the trick (as you can see I made the second left join a subquery):

SELECT Q.*, D.GRRCHTarg, D.GRRCHAct
FROM qryRpt5YrProgress4 AS Q LEFT JOIN (SELECT D.PkgID, D.TargetDate AS
GRRCHTarg, D.ActualDate AS GRRCHAct
FROM tblPkgDates AS D, tblPkgDateTypes AS DT
WHERE D.DateTypeID = DT.ID AND DT.DateType = 'GRRC Hearing' AND
(D.TargetDate IS NOT NULL OR D.ActualDate IS NOT NULL)) AS D ON
Q.[5YrPkgID]=D.PkgID;
 
Back
Top