Query table based on multiple keys

[dummie_q]

Hey,

I am having some confusion about how to formulate this particular
query.
I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with the
columns a1,a2,a4 forming the primary key. Table B again has 3 columns
with b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.
All columns are of the same datatype in both tables. Now I want to get
rows from table A which are not present in table B. Whats the best way
of doing this?

Thanks

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict235166.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725

[blueghost73]

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

--Richard

[--CELKO--]

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 );

[blueghost73]

--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

[Erland Sommarskog]

--CELKO-- (jc*******@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, es****@sommarskog.se

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

[Erland Sommarskog]

bl*********@yahoo.com (bl*********@yahoo.com) 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, es****@sommarskog.se

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

[--CELKO--]

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

Arrrgh! Cut & paste error!