implementing a check in the where clause

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

implementing a check in the where clause

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-25 : 08:36:32

hi there,

I am trying to pull using conditional switches in the where clause, and having a bit trouble in getting the desired information. e.g.

Declare @Channels table (ChannelID int, isCod bit)
Declare @Data table (CId int, Status_ID int)
Insert into @Channels values (1,1),(2,0)
Insert into @Data values (12806,6),(12807,6),(12808,6),(23792,12),(23782,12),(23722,12)
Select * from @Data

Select C.ChannelID,D.CId
from @Channels C
Inner Join @Data D on C.isCod=(Case
when C.isCod=0 and D.Status_ID=6 then 1
when C.isCod=0 and (D.Status_ID=6 or D.Status_ID=12) then 0
end)

if @Channels.isCod=0 then the data having Status_Id=6 should be pulled. But if @Channels.isCod=1 then the data having either status_ID=6 or Status_ID=12 should be pulled.

for the given input the information should be displayed in the following form.

ChannelId, CID
1 12806
1 12807
1 12808
1 23792
1 23782
1 23722
2 12806
2 12807
2 12808

this check will be implemented in the Where clause of a Stored procedure having different tables joined over required criterias. Many thanks!!

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-25 : 08:39:29

so far i have done but this is not giving me the required result.... confused as if i might be doing something wrong

Select C.ChannelID,D.CId
from @Channels C
Inner Join @Data D on C.isCod=(Case
when C.isCod=0 and D.Status_ID=6 then 1
when C.isCod=0 and (D.Status_ID=6 or D.Status_ID=12) then 0
end)
Order by 1

Cheers
MIK

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-25 : 12:43:07

how about this?

Declare @Channels table (ChannelID int, isCod bit)
Declare @Data table (CId int, Status_ID int)
Insert into @Channels values (1,1),(2,0)
Insert into @Data values (12806,6),(12807,6),(12808,6),(23792,12),(23782,12),(23722,12)
Select * from @Data


select x.ChannelID,x.CId 
from
(
Select C.ChannelID,C.isCod ,D.CId, d.Status_ID
from @Channels C
cross Join @Data D 
) x

where (x.isCod = 0 and x.Status_ID = 6)
or
	(x.isCod = 1 and (x.Status_ID = 6 or x.Status_ID = 12))
order by 
	x.ChannelID

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-25 : 14:29:07

quote:
Originally posted by MIK_2008

so far i have done but this is not giving me the required result.... confused as if i might be doing something wrong

Select C.ChannelID,D.CId
from @Channels C
Inner Join @Data D on C.isCod=(Case
when C.isCod=0 and D.Status_ID=6 then 1
when C.isCod=0 and (D.Status_ID=6 or D.Status_ID=12) then 0
end)
Order by 1

Cheers
MIK

Done, Fixed i just got dumb and was looking into the data of underlaying table ... the issue was in my code I stated in both cases isCod=0