How to Avoid Correlated Sub Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Avoid Correlated Sub Query

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-05-07 : 07:00:43

SELECT CASE WHEN AUDIT6 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT6)+' (6)'
WHEN AUDIT5 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT5)+' (5)'
WHEN AUDIT4 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT4)+' (4)'
WHEN AUDIT3 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT3)+' (3)'
WHEN AUDIT2 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT2)+' (2)'
WHEN AUDIT1 IS NOT NULL THEN (SELECT P.[Name] FROM Person P WHERE P.ID=F.AUDIT1)+' (1)' END
FROM dbo.AuditFile F

above query displays latest visit person name from person table.
can you please tell me how to avoid correlated sub query.
is there any other way.
its urgent.

$atya.

Love All Serve All.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-07 : 07:34:49

I suppose you want a solution that doesn't require you to normalize your 6 audit columns, like they should be?

SELECT p.Name + CASE WHEN P.ID=F.AUDIT6 THEN '(6)'
 WHEN P.ID=F.AUDIT5 THEN '(5)'
 WHEN P.ID=F.AUDIT4 THEN '(4)'
 WHEN P.ID=F.AUDIT3 THEN '(3)'
 WHEN P.ID=F.AUDIT2 THEN '(2)'
 WHEN P.ID=F.AUDIT1 THEN '(1)' END
FROM Person P 
INNER JOIN AuditFile F ON P.ID=COALESCE(F.AUDIT6,F.AUDIT5,F.AUDIT4,F.AUDIT3,F.AUDIT2,F.AUDIT1)

Warning: not tested.

If you can't redesign the AuditFile table to put the IDs in a single column, you can create a view that UNIONs the 6 columns into one and join that instead. Still not ideal but makes this query a little easier.

edit: added missing END keyword to CASE expression

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-07 : 07:43:21

and you need to avoid a correlated subquery because???
You have no normalization, but are worried about correlated subqueries...

It is not urgent enough that you can go ahead and use a correlated subquery to get the answer you need right away, but urgent nonetheless?

I do not understand... at all...

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 08:29:05

Same answer was given to him yesterday here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125177

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources