Re: SQL subquery question

Geoffrey Poole wrote: > > What is the easiest way to write the following SQL :- > > select * from a where (x,y) not in (select x,y from b);> > I've a foreign key constraint on 2 columns that has failed to build so > I'm trying to find the offending rows. No version info Geoff so, if you have 7.31+: SELECT a.* FROM a LEFT OUTER JOIN b ON a.x = b.x AND a.y = b.y WHERE b.x IS NULL; If you have an earlier verion there is no way to do it in a single statement, you have to use a temp table: SELECT a.*, b.x b_x FROM a, OUTER b WHERE a.x = b.x AND a.y = b.y INTO TEMP fred; SELECT <all columns except b_x> FROM fred WHERE b_x IS NULL; Art S. Kagel

Art S. Kagel wrote: Notice the three VERY different approaches to this problem, once again proving Kagel's First Law of SQL! Vis: "There are at LEAST three ways to perform ANY query in SQL." Art S. Kagel > Geoffrey Poole wrote: > > > > What is the easiest way to write the following SQL :- > > > > select * from a where (x,y) not in (select x,y from b);> > > > I've a foreign key constraint on 2 columns that has failed to build so > > I'm trying to find the offending rows. > > No version info Geoff so, if you have 7.31+: > > SELECT a.* > FROM a LEFT OUTER JOIN b > ON a.x = b.x AND a.y = b.y > WHERE b.x IS NULL; > > If you have an earlier verion there is no way to do it in a single > statement, you have to use a temp table: > > SELECT a.*, b.x b_x > FROM a, OUTER b > WHERE a.x = b.x AND a.y = b.y > INTO TEMP fred; > > SELECT <all columns except b_x> > FROM fred > WHERE b_x IS NULL; > > Art S. Kagel