Query table based on multiple keys

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 );
 >> Stay informed about: Query table based on multiple keys 

--CELKO-- wrote:
 > 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 );


Yeah, that works better than my version. I just tested a little more
and realized that mine doesn't actually do what I expected, but I can't
figure out why not.

--Richard<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query table based on multiple keys 

--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.TakeThisOut@sommarskog.se

Books Online for SQL Server SP3 at
<a style='text-decoration: underline;' href="http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp" target="_blank">http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp</a><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query table based on multiple keys 

( ) writes:
 > I'm no expert, so this probably isn't the most efficient way to do
 > this, but I think this will work:
 >
 > select A.* from A, B
 > where A.a1 *= B.b1
 > and A.a2 *= B.b2
 > and A.a4 *= B.b4
 > and B.b1 is null

*= is a older form of outer join which has all sorts of funny
quirkes with it. I am not going to find why this does not work.

Use the new ANSI syntax instead:

select A.*
from A
left join B ON A.a1 = B.b1
and A.a2 = B.b2
and A.a4 = B.b4
where and B.b1 is null

But I much prefer NOT EXISTS for this type of query, as it much better
expresses what you are looking for.


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

Books Online for SQL Server SP3 at
<a style='text-decoration: underline;' href="http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp" target="_blank">http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp</a><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query table based on multiple keys 

  >> What is that *= doing on the last row? <<

Arrrgh! Cut & paste error!<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Query table based on multiple keys