Re: Question about advanced SQL
| From: | Bill Morrow | Date: | Thu, 28 Feb 2002 03:12:16 +0000 |
| Subject: | Re: Question about advanced SQL | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-17345@lists.php.net to get a copy of this message | ||
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
>
--