Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
Shouldn't it be
WHERE T1.TranType ='DbtPro' rather than WHERE T2.TranType ='DbtPro' -- Regards, Tom Ogilvy "ExcelMan" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
WHERE T2.TranType ='DbtPro' or T2.TranType is null -- Rob van Gelder - http://www.vangelder.co.nz/excel "ExcelMan" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
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" wrote in message ... WHERE T2.TranType ='DbtPro' or T2.TranType is null -- Rob van Gelder - http://www.vangelder.co.nz/excel "ExcelMan" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
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. -- Rob van Gelder - http://www.vangelder.co.nz/excel "ExcelMan" wrote in message ... 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" wrote in message ... WHERE T2.TranType ='DbtPro' or T2.TranType is null -- Rob van Gelder - http://www.vangelder.co.nz/excel "ExcelMan" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Recordset Problem -- Left Join
ExcelMan wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem copying ADO recordset to Excel 2003 | Excel Programming | |||
problem in seek value in recordset with index. | Excel Programming | |||
ADODB Recordset problem | Excel Programming | |||
Recordset Problem - object is closed | Excel Programming | |||
Conceptual Problem with DAO/ADO Recordset | Excel Programming |