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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: