I know I’ve covered sub queries before, but I wanted to make sure I made a specific point. When you create a correlated sub query, you can reference columns from your subquery anywhere in your outer query. Usually you’ll either use it in your SELECT clause or your WHERE clause, but you can use it anywhere you’d use a column.
You just need to make sure you alias your tables, so you can properly identify which column you’re trying to reference.
One quick note: you can run these queries against your AdventureWorks database, the 2008 version.
Check out this query:
SELECT DISTINCT
pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1
WHERE pv1.ProductID IN
(SELECT pv2.ProductID
FROM Purchasing.ProductVendor pv2
WHERE pv1.BusinessEntityID <> pv2.BusinessEntityID)
ORDER BY pv1.BusinessEntityID
In this query we only used the pc2.productID in the inner query. You should be used to that by now. Here’s something you may not have known you could do.
SELECT DISTINCT pv1.ProductID, innerQuery.BusinessEntityID FROM Purchasing.ProductVendor pv1 INNER JOIN ( SELECT pv2.ProductID, pv2.BusinessEntityID FROM Purchasing.ProductVendor pv2) innerQuery ON pv1.ProductID = innerQuery.productID ORDER BY pv1.BusinessEntityID
In this query we got the BusinessEntityID value from the innerQuery. I know, this is a contrived example, but I wanted to show you how to use aliases with correlate sub queries, and how to reference them all.
I’ll be honest, I still get tripped up by aliasing every once in a while. When you get an odd result from your queries, chances are you can look at your alias references, and find the one you’ve missed. When that fails, always have someone you can bounce your queries off of for a sanity check. Otherwise, you may need a short vacation to Beautiful Arkham, the gardens really are lovely this time of year!
As always, let me know if you have any questions!