Correlated Subquery with CASE in SQL Server 2005

Correlated subquery is always a powerful tool in developer’s toolkit. It has been proved very efficient many time against CURSOR and give good performance as well. Let me show you one good example of correlated subquery with CASE expression in Microsoft SQL Server 2005.

--create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10)

)

GO

--insert records in above table

INSERT INTO CustInfo

SELECT 'RITSEH' UNION ALL

SELECT 'RAJAN'

GO

--create table2 for demo

CREATE TABLE OrderMasters

(

OrderMasterId INT IDENTITY(1,1),

CustId INT,

Quantity INT

)

--insert records in above table

INSERT INTO OrderMasters

SELECT 1,10 UNION ALL

SELECT 1,14 UNION ALL

SELECT 1,2

--co-related subquery with CASE

SELECT C.CustId,C.Name,

CASE(SELECT count(CustId) FROM OrderMasters O WHERE O.CustId=C.CustID)

WHEN 0 THEN 'No Order'

ELSE 'Has Order'

END as Status

FROM

CustInfo C

Above, correlated query will return “No Order” if it will not find any records in OrderMasters table and it will return “Has Order” if there is an entry in OrderMasters table.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com