HELP: SQL LEFT JOIN not working 
Author Message
 HELP: SQL LEFT JOIN not working

Hello,

I am using ADO 2.1 to connect to a SQL Server database.  I am trying to find
out if there are any records in the BATCHMGR table that do not have any
counterparts in the INDEXING table.

Here is my SQL statement:

    sSQL = "SELECT BATCHMGR.* " & _
            "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
INDEXING.BatchNo " & _
            "WHERE (((INDEXING.BatchNo) Is Null))"

If I create an access query it works fine, but when I use it with ADO/VB I
get an empty recordset returned.  If I try to see the record count I get:
"The data provider or other service returned an E_FAIL status."

Even if I take away the where clause I still get zero hits:
    sSQL = "SELECT BATCHMGR.* " & _
            "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
INDEXING.BatchNo "

Any ideas?

-John



Mon, 30 Sep 2002 03:00:00 GMT  
 HELP: SQL LEFT JOIN not working


Fri, 19 Jun 1992 00:00:00 GMT  
 HELP: SQL LEFT JOIN not working
I don't see anything that's definately wrong with what you're doing here,
but here are a few possible fixes...

1)    If you haven't already done so, try testing your query through Query
Analyzer, rather than Access.  If it works, you should be able to run it
through ADO, and you might want to start looking at other things, like your
Connection String and Recordset Cursor settings.

2)    "INDEXING" looks suspiciously like it could be a reserved word (even
though I couldn't find it in Books On Line).  Try Delimiting the table name
as [INDEXING]

3)    If all else fails, use a subquery instead of a left join.
    sSQL = "SELECT b.* " _
            & "FROM BATCHMGR b " _
            & "WHERE b.BatchNo NOT IN " _
            & "(SELECT DISTINCT i.BatchNo FROM INDEXING i)"


Quote:

> Hello,

> I am using ADO 2.1 to connect to a SQL Server database.  I am trying to
find
> out if there are any records in the BATCHMGR table that do not have any
> counterparts in the INDEXING table.

> Here is my SQL statement:

>     sSQL = "SELECT BATCHMGR.* " & _
>             "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
> INDEXING.BatchNo " & _
>             "WHERE (((INDEXING.BatchNo) Is Null))"

> If I create an access query it works fine, but when I use it with ADO/VB I
> get an empty recordset returned.  If I try to see the record count I get:
> "The data provider or other service returned an E_FAIL status."

> Even if I take away the where clause I still get zero hits:
>     sSQL = "SELECT BATCHMGR.* " & _
>             "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
> INDEXING.BatchNo "

> Any ideas?

> -John



Tue, 01 Oct 2002 03:00:00 GMT  
 HELP: SQL LEFT JOIN not working
Try LEFT OUTER JOIN


Quote:
> I don't see anything that's definately wrong with what you're doing here,
> but here are a few possible fixes...

> 1)    If you haven't already done so, try testing your query through Query
> Analyzer, rather than Access.  If it works, you should be able to run it
> through ADO, and you might want to start looking at other things, like
your
> Connection String and Recordset Cursor settings.

> 2)    "INDEXING" looks suspiciously like it could be a reserved word (even
> though I couldn't find it in Books On Line).  Try Delimiting the table
name
> as [INDEXING]

> 3)    If all else fails, use a subquery instead of a left join.
>     sSQL = "SELECT b.* " _
>             & "FROM BATCHMGR b " _
>             & "WHERE b.BatchNo NOT IN " _
>             & "(SELECT DISTINCT i.BatchNo FROM INDEXING i)"



> > Hello,

> > I am using ADO 2.1 to connect to a SQL Server database.  I am trying to
> find
> > out if there are any records in the BATCHMGR table that do not have any
> > counterparts in the INDEXING table.

> > Here is my SQL statement:

> >     sSQL = "SELECT BATCHMGR.* " & _
> >             "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
> > INDEXING.BatchNo " & _
> >             "WHERE (((INDEXING.BatchNo) Is Null))"

> > If I create an access query it works fine, but when I use it with ADO/VB
I
> > get an empty recordset returned.  If I try to see the record count I
get:
> > "The data provider or other service returned an E_FAIL status."

> > Even if I take away the where clause I still get zero hits:
> >     sSQL = "SELECT BATCHMGR.* " & _
> >             "FROM BATCHMGR LEFT JOIN INDEXING ON BATCHMGR.BatchNo =
> > INDEXING.BatchNo "

> > Any ideas?

> > -John



Thu, 03 Oct 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. LEFT JOIN does not work in query

2. Left Join Does Not Work

3. Left Join Does Not Work

4. need help with left join sql

5. VB6 - Left() Sytax help not working

6. SQL LEFT JOINS

7. CR8 / SQL / Aliases and Left joins

8. CR 8 / SQL 7.0 / left join

9. SQL statement on 2 or more tables (left outer joins)

10. Left Outer Join to SQL Server

11. SQL LEFT JOINS

12. LEFT JOIN not selecting all records in originating table

 

 
Powered by phpBB® Forum Software