Re: Question about advanced SQL

Hi,

        I assumed that you was used MySQL for database.  With MySQL you can used this SQL to find different row

        select tblcd.* from tblcd left join tblorder on tblcd.cdid=tblorder.cdid where tblorder.cdid is null

sommai At 12:37 28/2/2002 +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

---------------------------------------------------------- Please be informed that all e-mail which are addressing to thaithanakit.co.th will need to be changed to BTsecurities.com by March 1, 2002 Thank you. :-) ----------------------------------------------------------