+ Reply to Thread
Results 1 to 7 of 7

ADO Recordset Problem -- Left Join

  1. #1
    ExcelMan
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: ADO Recordset Problem -- Left Join

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

    rather than
    WHERE T2.TranType ='DbtPro'

    --
    Regards,
    Tom Ogilvy


    "ExcelMan" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Rob van Gelder
    Guest

    Re: ADO Recordset Problem -- Left Join


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

    --
    Rob van Gelder - http://www.vangelder.co.nz/excel


    "ExcelMan" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    ExcelMan
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > WHERE T2.TranType ='DbtPro'
    > or T2.TranType is null
    >
    > --
    > Rob van Gelder - http://www.vangelder.co.nz/excel
    >
    >
    > "ExcelMan" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #5
    Rob van Gelder
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >>
    >> WHERE T2.TranType ='DbtPro'
    >> or T2.TranType is null
    >>
    >> --
    >> Rob van Gelder - http://www.vangelder.co.nz/excel
    >>
    >>
    >> "ExcelMan" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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. #6
    Jamie Collins
    Guest

    Re: 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.

    --


  7. #7
    Jamie Collins
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1