Can you explain these query results?

[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.

 

[KARL DEWEY]

Try dropping LEFT JOIN tblPkgDateTypes AS DT ON D.DateTypeID = DT.ID and
see what you get.

 

[Michel Walsh]

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]

If I remove the second left join and where clause, I get 10550 rows,
completely not what I want.

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

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.

 

[mscertified]

Thanks for the response.
I thought you might have the answer but I still get only 1645 rows with your
modification.

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

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.

 

[mscertified]

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;