SELECT
        tblCD.cdID,
        tblCD.cdTitle,
        tblCD.cdArtist
FROM
        tblCD,
        tblOrders
WHERE
        tblOrders.cdID = tblCD.cdID
        AND tblCD.cdID NOT IN (
                SELECT cdID FROM tblOrders
        )

The support for this sort of "nested query" will very between RDBMSs but the
statement above is most generic. Databases like postgres will not only
support this but they will also have proprietary ways of achiveing the same
thing more efficiently.

Some databases also support LEFT JOIN, OUTER JOIN directives but support and
implementation will vary. I havn't tested the above query but it should work
:-)
Although it probably isn't the most efficient.

[TK] 

> -----Original Message-----
> From: Adam Royle [mailto:[EMAIL PROTECTED]] 
> Sent: Thursday, 28 February 2002 1:38 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] Question about advanced SQL
> 
> 
> 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