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
