On 21 January 2011 11:58, Karl DeSaulniers <karl@xxxxxxxxxxxxxxx> 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
