|
Home > Archive > microsoft.public.sqlserver.server > June 2002 > SQL Join View Statement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Join View Statement
|
|
| Victor 2002-06-27, 8:25 pm |
| 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?
| |
| Edward Bogaard 2002-06-28, 4:25 am |
| >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
| |
| Peter Hyssett 2002-06-28, 7:25 am |
| 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
>.
>
| |
| Roy Harvey 2002-06-28, 7:25 am |
| 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
|
|
|
|
|