Query - SQL Server Forums

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

SQL Server Development (2000)

Query

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-12-06 : 06:11:17

Hi,

I have 2 tables as follows:

Table A:

HelpFileID FormName Area URL
---------------------------------------------------------------------
1 frmOne Maintenance http://test
2 frmOne Reports http://test2
3 frmTwo null http://test3
4 frmTwo null http://test3

Table B:

HelpFileID CountryCode URL Visible
---------------------------------------------------------------------
3 DE http://test4
3 HK http://test5

I need to write a query that will retrieve the URL from table B whenever it exists for a given FormName / Area / CountryCode combination.

i.e. my stored proc would take the the FormName / Area / CountryCode and return the appropriate URL from table B. If the country doesn't exist in Table B then it should get the URL from table A.

I have written a proc that does the trick but it has lots of "IF THEN" statements. Is there a better way to write this?

Thanks,
Kabir

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-06 : 06:30:35

[code]
declare @taba table (HelpFileID int, FormName varchar(10), Area varchar(20), URL varchar(30))

insert into @taba
select 1, 'frmOne', 'Maintenance', 'http://test'
union select 2, 'frmOne', 'Reports', 'http://test2'
union select 3, 'frmTwo', null, 'http://test3'
union select 4, 'frmTwo', null, 'http://test3'

declare @tabb table (HelpFileID int, CountryCode char(2), URL varchar(30))

insert into @tabb
select 3, 'DE', 'http://test4'
union select 3, 'HK', 'http://test5'

select distinct
a.HelpFileID,
a.FormName,
a.Area,
Case when b.URL is null then a.URL else b.URL end as URL
from @taba a
left join @tabb b
on b.HelpFileID = a.HelpFileID
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 07:24:19

and

Case when b.URL is null then a.URL else b.URL end

is the extention of

COALESCE(b.URL,a.URL)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources