Compare Value From SubQuery to Main 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

SQL Server 2000 Forums

Transact-SQL (2000)

Compare Value From SubQuery to Main Query

Author

Topic

wabs27
Starting Member

4 Posts

Posted - 2008-05-02 : 14:33:24

Hi,

I have a complex query in SQL 2000 that looks something like the following:

SELECT A, B, C, Coalesce(D, (Select E from Table2 WHERE F=A)) AS MyVal
FROM TABLE1

The problem is that I can't figure out how to make the WHERE statement in the COALESCE work. I want it to be where F is equal to the A that was select for that row.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-02 : 14:42:58

Use a join instead of a subquery.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 14:47:34

[code]SELECT t1.A, t1.B, t1.C, Coalesce(t1.D,t1.E) AS MyVal
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.F=t1.A[/code]

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-03 : 02:45:49

hi visakh,
i think a small correction in ur query needed,
only a substitution..
SELECT t1.A, t1.B, t1.C, Coalesce(t1.D,t2.E) AS MyVal
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.F=t1.A

ok
tanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 02:48:06

quote:
Originally posted by soorajtnpki

hi visakh,
i think a small correction in ur query needed,
only a substitution..
SELECT t1.A, t1.B, t1.C, Coalesce(t1.D,t2.E) AS MyVal
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.F=t1.A

ok
tanx

Yup. nice spot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources