ADO Recordset Problem -- Left Join

[ExcelMan]

I'm trying to execute a query that joins two Excel tables. I need all the
records in the first table and those in the second table that match the (2
field) key in the second table -- the typical LEFT JOIN query situation.

Problem is, the recordset being returned only seems to bring back records
that match on fields in BOTH tables, as if I were doing an equi-join. I
don't understand this behavior, especially in light of my successful
execution of a RIGHT JOIN query moments earlier in the code.

Is there something I need to do to clear things out? Shown below is the SQL
used in the query. Thanks for your help.

SELECT T1.*, T2.* FROM tblEqpReportTLTempTbl As T1 LEFT JOIN
TranDataActTblTLTempTbl As T2 ON (T1.TranName = T2.TranName AND T1.Fund =
T2.Fund) WHERE T2.TranType ='DbtPro'

There are 76 records in tblEqpReportTLTempTL and 39 records in
TranDataActTblTLTempTbl that have TranType = 'DbtPro'. When I run the
query I get a recordset returned with 39 records. I should be getting 76.

Thanks.

 

[Tom Ogilvy]

Shouldn't it be
WHERE T1.TranType ='DbtPro'

rather than
WHERE T2.TranType ='DbtPro'

 

[Rob van Gelder]

WHERE T2.TranType ='DbtPro'
or T2.TranType is null

 

[ExcelMan]

Rob,

Thanks for your suggestion. I tried it as is and it still didn't seem to
work. Still, I understand the principle you are making. So I rewrote the
query as follows:

SELECT T1.*, T2.* FROM tblEqpReportTLTempTbl As T1 LEFT JOIN (Select *
from TranDataActTblTLTempTbl WHERE TranType = 'DbtPro') T2 ON T1.TranName =
T2.TranName AND T1.Fund = T2.Fund

Now it works great.

Thanks.

 

[Rob van Gelder]

Subquery... good work.

I was about to reply to my post - only if TranType was no-null would it
return correct results.
Your solution is better.

 

[Jamie Collins]

typical LEFT JOIN query situation.

Problem is, the recordset being returned only seems to bring back records
that match on fields in BOTH tables, as if I were doing an equi-join. I
don't understand this behavior

SELECT T1.*, T2.* FROM tblEqpReportTLTempTbl As T1 LEFT JOIN
TranDataActTblTLTempTbl As T2 ON (T1.TranName = T2.TranName AND T1.Fund =
T2.Fund) WHERE T2.TranType ='DbtPro'

Are you using MS Access/Jet? (the use if tbl as a prefix is a MS Access
'hallmark' <g>) If so, the explanation is that Jet does not support
ANSI SQL-92 OUTER JOIN syntax. You should be able to specify TranType
in the JOIN criteria e.g.

SELECT T1.*, T2.*
FROM tblEqpReportTLTempTbl As T1
LEFT JOIN TranDataActTblTLTempTbl As T2
ON T2.TranType ='DbtPro'
AND T1.TranName = T2.TranName
AND T1.Fund = T2.Fund;

This gives the desired result set e.g. with SQL Server. However, try it
with Jet and you get an error, 'Join expression not supported.'
Jamie.

--