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

Reply via email to