Re: [PHP-DB] Re: Getting Results
On 21 January 2011 11:58, Karl DeSaulniers k...@designdrumm.com wrote: I did not know you could do a SELECT inside a SELECT. Commonly known as a sub select. You can use them like ... SELECT columns FROM ( SELECT columns FROM table) WHERE column IN (SELECT column FROM table) As part of a FROM or as part of a WHERE ... IN clause. They are the probably the most common ones. You also have (depending upon your SQL engine and version) something called common table expressions. (From MS SQL Books Online) ... USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO CTE's are great for recursive queries, once you get your head around them. I also use them to help me find the next and previous row to the current row in a result set, where a single table is essentially bound 3 times, but with the CTE, additional optimization seems to be in play and work a LOT faster overall. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
Ahh yes, I remember that now. Thank you. Karl On Jan 21, 2011, at 6:17 AM, Richard Quadling wrote: On 21 January 2011 11:58, Karl DeSaulniers k...@designdrumm.com wrote: So to do descending order I'd put DES or DEC? Just curious about that one. ASC and DESC -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
Very informative, Thank you Richard. I will have to flag this one and come back to it. My database structure may require something of this measure down the line with the auditing ability I plan on building into the admin section. Never heard of USE AdventureWorks. Is that a SQL system function? Or just a table in the examples database? On Jan 21, 2011, at 6:16 AM, Richard Quadling wrote: You also have (depending upon your SQL engine and version) something called common table expressions. (From MS SQL Books Online) ... USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO Guess I will have to do some googling.. :) Thanks again. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
On 21 January 2011 12:39, Karl DeSaulniers k...@designdrumm.com wrote: Very informative, Thank you Richard. I will have to flag this one and come back to it. My database structure may require something of this measure down the line with the auditing ability I plan on building into the admin section. Never heard of USE AdventureWorks. Is that a SQL system function? Or just a table in the examples database? USE [1] is a Transact SQL statement (MS call their SQL language T-SQL). AdventureWorks is one of the demo DBs MS supply to help people learn about using their server. Regards, Richard. [1] http://msdn.microsoft.com/en-us/library/ms188366.aspx -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
