I have a problem displaying the correct results in a table view with a
left join. I have two tables in a non-identifying referential
relationship. Table A is the table that contains the data. Table B is
the XREF table. Now I want to display the results for table A were the
ID column in both tables do not match(<> ). The statement looks like
this:
SELECT *
FROM Table.A LEFT OUTER JOIN Table.B ON Table.A_ID = Table.B_ID
WHERE Table.A_ID <> Table.B_ID

By the way when I enter this query statement into a ms sql view, it
replaces the WHERE with AND.

In other words all the entries in Table.A that is not XREFED in
Table.B. I am sure that this is a common problem in sql statements and
that the solution is simple but since I am new to this could somebody
please help me?

Report this post to a moderator

>SELECT *
>FROM Table.A LEFT OUTER JOIN Table.B ON Table.A_ID =
Table.B_ID
>WHERE Table.A_ID <> Table.B_ID

I'm guessing you want

SELECT * FROM Table.A
WHERE NOT EXISTS (SELECT 1 FROM Table.B WHERE Table.B_ID =
Table.A_ID)

or vice versa (everything from B that's not in A).

Of course, not exists will cause a table scan.

Regards,
Edward

Report this post to a moderator

Try adding to the query:
AND Table.B_ID IS NULL

>-----Original Message-----
>>SELECT *
>>FROM Table.A LEFT OUTER JOIN Table.B ON Table.A_ID =
>Table.B_ID
>>WHERE Table.A_ID <> Table.B_ID
>
>I'm guessing you want
>
>SELECT * FROM Table.A
>WHERE NOT EXISTS (SELECT 1 FROM Table.B WHERE Table.B_ID
=
>Table.A_ID)
>
>or vice versa (everything from B that's not in A).
>
>Of course, not exists will cause a table scan.
>
>Regards,
>Edward
>.
>

Report this post to a moderator

Victor,

>SELECT *
>FROM Table.A LEFT OUTER JOIN Table.B ON Table.A_ID = Table.B_ID
>WHERE Table.A_ID <> Table.B_ID

>In other words all the entries in Table.A that is not XREFED in
>Table.B.

The approach Edward gave, using EXISTS, is the best way to handle this
type of query, but I want to try to explain why your example wasn't
working.

When using an outer join, in the case where you get a row returned
from table A without a matching row from table B, the columns of the
missing table are NULL. In your query you are testing for equality:

WHERE Table.A_ID <> Table.B_ID

but NULL can not be compared at all. NULL is never equal to anything,
not even another NULL. And NULL is never NOT equal to anything
either. The only valid way to test a NULL is with the IS NULL and IS
NOT NULL test.

In this case, since table B is the one that could be NULL, the WHERE
clause could have been coded as:

WHERE Table.B_ID IS NULL

But the best way to do this test, in my opinion, is to use the EXISTS
test that Edward posted.

Roy

Report this post to a moderator