Posted by Erland Sommarskog on 06/25/05 01:05

--CELKO-- (jcelko212@earthlink.net) writes:
> SELECT a1, a2, a3, a4
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.a1 = B.b1
> AND A.a2 = B.b2
> AND A.a3 = B.b3
> AND A.a4*= B.b4 );

What is that *= doing on the last row?

The requirements were somewhat ambiguous, but one of these should do:

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4 );

(Rows identified by keys, the value in the non-key column a3/b3 may
be different.)

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4
AND A.a3 = B.b3 );

(Rows may be in both tables, but may have a difference in a3/b3.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]