[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
RE: [PHP-DB] Question about advanced SQL
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
Re: [PHP-DB] Question about advanced SQL
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
Re: [PHP-DB] 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. :-) -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
