CASE in Inner Join - 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)

CASE in Inner Join

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-12-23 : 13:39:43

Hi,

I need to know if I can use the CASE statement in INNER JOIN.

Lets say I have a variable "monthslist" which is varchar.
This variable is a comma seperated list of months. If we have to denote all months then I get 13.
So @monthslist = '8,9,10' means August ,sep and Oct and
@monthslist = '13' means the whole year.

I have a function that will take this months CSV and return a table with each one in a row. Then I use this table and do Inner Join to get the Months requested. But if the user requests the whole year I do not have to do the inner Join.

so how can I do this

Declare @monthslist varchar(50)
Set @monthslist = '13'

select * from A inner Join B On A.ID = B.ID

CASE when @monthslist <> '13'
INNER JOIN dbo.f_getmon_csv(@monthslist) as MN On MN.Month = A.Month
END

I hope my question is clear enough.

Thanks
Maximus

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-23 : 13:47:33

I don't think your allowed to have a CASE statement like that. However You could use an if clause and perform 2 different queries.


IF(@monthslist = '13')
BEGIN
select * from A inner Join B On A.ID = B.ID 
END
ELSE
BEGIN
select * from A inner Join B On A.ID = B.ID 
INNER JOIN dbo.f_getmon_csv(@monthslist) as MN On MN.Month = A.Month
END

Dustin Michaels

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-23 : 13:57:11

Another option would be to have the udf return months 1-12 when the input = 13 and always include the join.

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-12-23 : 13:58:49

Hi Dustin,

Right now I am doing that but I am wondering if there is a better way to accomplish this.

Thanks
maximus

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-12-23 : 14:00:11

Hi,
Then unnecessarily we are doing a JOIN ... correct ?
Thanks
maximus

quote:
Originally posted by ehorn

Another option would be to have the udf return months 1-12 when the input = 13 and always include the join.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-23 : 14:04:29

My guess is adding 12 additional rows will make little performance difference. But you can test it and see. :)

Here is another method using some OR logic

select * from a
inner join b on a.id = b.id
where 
( @monthslist = 13 )
or
( @monthslist != 13 and a.month in (select month from dbo.f_getmon_csv(@monthslist)) )

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-23 : 15:11:18

Never use an OR or a conditional CASE expression on a join. I can't stress this enough. There might be a situation where this is necessary once in a great while, but in 99.99999% of all cases you should never do this.

Use a left outer join to your Months. If you want to conditionally SELECT a column from the outer table based on some condition, use a CASE in your SELECT portion, not in the join condition. If you need to return rows based on a condition, put it in the WHERE clause.

Your example might best be written like this:


select 
  a.* 
from 
  YourTable a
left outer join 
  dbo.MonthList(@MonthsList) b
on 
  a.Month= b.Month
where 
  @MonthsList = '13' or b.Month is not null

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources