Correlated subqueries -section 2

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
----------------------------------------------------------
Correlated subqueries, unlike self-contained subqueries discussed above, this subquery references a column in the outer query thus we cannot select a correlated subquery to test run it. Both outer and inner query must run at once.

Same table correlation: Both outer and inner query refer to different instances of the same table.

-- Problem statement:  Write a query to return products with the maximum unit price in each category.

SELECT ProductName
     , UnitPrice
	 , CategoryID
FROM dbo.Products AS t1                              -- Same table correlation subquery, example 2.1
WHERE unitprice =
(SELECT MAX(unitprice)
FROM dbo.Products AS t2
WHERE t2.categoryid = t1.categoryid);

Open in new window


To produce the expected results, the same table (dbo.Products) is aliased as t1 and t2 to produce two instance of the same table to be used independent of each other each in the outer and inner query .

Different table correlation: The outer query (Customers) and the inner query (Orders) refer to instances of two different tables.

-- Problem statement: Write a query to return CompanyName for OrderDate '1997-01-16'.

SELECT CompanyName                                   
FROM dbo.Customers AS C                              -- Different tables correlation subquery, example 2.2
WHERE EXISTS    -- exist here returns true or false 
(SELECT * FROM dbo.Orders AS O
 WHERE O.CustomerID = C.CustomerID 
       AND O.OrderDate = '1997-01-16');

-- ... WHERE NOT EXISTS(SELECT * FROM …    <-- use this when required

Open in new window


Table Expressions are named queries. You a) write an inner query that returns a relational result set, b) name it, and c) query it from an outer query. T-SQL supports four forms of table expressions:
a) Derived tables and b) Common table expressions (CTEs) are created and used in code (non-db object).
c) Views and d) Inline table-valued functions are created in code or in SSMS stored as db objects.

Edit ends here:
----------------------------------------------------------
also see: http://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Note, Questions on the remaining sections will be added later.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
Change "query thus" to "query. Thus"
Maybe change "each other each" to "each other"?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Done.

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Proofreading

From novice to tech pro — start learning today.