selecting same value from multiple tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

selecting same value from multiple tables?

Author

Topic

henderson.marty
Starting Member

6 Posts

Posted - 2008-08-08 : 04:14:19

Hi folks,
I have a situation where I need to pick a particular row from one of 4 tables. Basically if there are 4 tables, A B C D it should get it in from tables (in order of precedence) D C B A.

So ideally if found in D it will use that, else C, B, A.

Is there an elegant way to do this, or just use:

IF EXISTS (SELECT VALUE FROM D WHERE DAY = @DAY)
SELECT VALUE FROM D WHERE DAY = @DAY

ELSE IF EXSISTS (SELECT VALUE FROM C WHERE DAY = @DAY)
SELECT VALUE FROM C WHERE DAY = @DAY

ELSE IF EXSISTS (SELECT VALUE FROM B WHERE DATE = @DATE)
SELECT VALUE FROM B WHERE DATE = @DATE

ELSE IF EXSISTS (SELECT VALUE FROM A WHERE DATE = @DATE)
SELECT VALUE FROM A WHERE DATE = @DATE

thanks.

pootle_flump

1064 Posts

Posted - 2008-08-08 : 06:57:59

You are hitting each table twice. How about:


SELECT VALUE FROM D WHERE DAY = @DAY

IF @@ROWCOUNT = 0 
BEGIN
SELECT VALUE FROM C WHERE DAY = @DAY

IF @@ROWCOUNT = 0 
BEGIN
SELECT VALUE FROM B WHERE DATE = @DATE

IF @@ROWCOUNT = 0 
BEGIN
SELECT VALUE FROM A WHERE DATE = @DATE

END
END
END

Or you could do it with a single query, outer joins and COALESCE, but this would mean hitting all four tables irrespective of whether or not you need to.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 07:02:39

that may return multiple result set to the client
Slight modification to it

DECLARE @VALUE INT
SELECT @VALUE = VALUE FROM D WHERE DAY = @DAY

IF @@ROWCOUNT = 0 
BEGIN
SELECT @VALUE = VALUE FROM C WHERE DAY = @DAY

IF @@ROWCOUNT = 0 
BEGIN
SELECT @VALUE = VALUE FROM B WHERE DATE = @DATE

IF @@ROWCOUNT = 0 
BEGIN
SELECT @VALUE = VALUE FROM A WHERE DATE = @DATE

END
END
END

SELECT @VALUE as VALUE

KH
[spoiler]Time is always against us[/spoiler]

pootle_flump

1064 Posts

Posted - 2008-08-08 : 07:21:02

quote:
Originally posted by khtan

that may return multiple result set to the client

I think you mean "often will"

Good catch and correction

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 07:39:24

Not if the record is usually found in table D

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources