select * from tblCd where cdId not in (select distinct cdId from tblOrders);
On Thu, Feb 28, 2002 at 12:37:49PM +1000, Adam Royle wrote: > Hi. > > I need some guidance for writing an effective query (instead of processing > through PHP). > > I lets says I have two tables, tblCDs and tblOrders. > > tblCD > > cdID | cdTitle | cdArtist > ------------------------------------------ > 1 | Great Hits | Bon Jovi > 2 | Forever Young | The Youngsters > 3 | Now and Then | Beach Boys > 4 | Cheesy Name | Two Tones > > > tblOrders > > orderID | cdID_FK | ordererID > ------------------------------------------ > 1 | 1 | 442 > 2 | 3 | 233 > 3 | 1 | 233 > > > Now, I want to select all the records from tblCD where the cdID does not > appear in any row of tblOrders.cdID_FK > > This means that it selects all the CDs that have not been ordered. > > The results of the query should be > > > cdID | cdTitle | cdArtist > ------------------------------------------ > 2 | Forever Young | The Youngsters > 4 | Cheesy Name | Two Tones > > > I know how I can do this in PHP (two queries - put all cdID_FK in an array, > and on displaying the other query, check if it is in the array, and display > if not) but there HAS to be an easier way. > > Adam. > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
