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

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

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

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

--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/techinf...2000/books.asp

blueghost73@yahoo.com (blueghost73@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, esquel@sommarskog.se

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

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

Arrrgh! Cut & paste error!