Dynamic "WHERE" - 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)

Dynamic "WHERE"

Author

Topic

bapugandu77
Starting Member

2 Posts

Posted - 2006-04-12 : 14:53:55

here is a table CS which has 2 Cols: CndID and SkillID

example CS:
CndID SkillID
1     4
1     6
2     8
2     4
2     10
3     4
3     8

I want to construct a query which works something like this:
SELECT CndID FROM CT WHERE SkillID = 8 AND SkillID = 10

The WHERE clause is dynamic and changes in value and length depending on the selected values in a listbox

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-04-12 : 15:25:34

Something like:

Declare @values varchar(8000)
Declare @sql varchar(8000)

set @values = '8,10'

set @sql = 'SELECT CndID FROM CT WHERE SkillID IN (' + @values + ')'

exec(@sql)

~Travis

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-12 : 16:07:55

I think you are talking about relational division.
We can go from simple:

select CndID from #CS
where skillid in(8, 10)
group by CndId
having count(*) = 2

-- note you have to dynamically construct the in clause, and the count of values somehow.

-- to more complex division
-- here I store the skills to search in a table, then divide the original CS table with the skills table:

create table #CS(CndID int, SkillID int)
insert #CS
select 1, 4
union all select 1 , 6
union all select 2 , 8
union all select 2 , 4
union all select 2 , 10
union all select 3 , 4
union all select 3 , 8

create table #CSkillsToSearch(skillID int)
insert #CSkillsToSearch
select 8 union select 10

-- this is my favored and perhaps most straight forward method.
select CndID from #CS join #CSkillsToSearch
on #CS.skillid = #CSkillsToSearch.skillid
group by CndId
having count(*) = (select count(*) from #CSkillsToSearch)

select distinct CndId
from #CS A
where not exists (
select CndId
from #CSkillsToSearch B
where not exists (
select *
from #CS CC
where A.CndId = CC.CndId
and B.SkillID = CC.SkillID ))

drop table #CS,#CSkillsToSearch

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-13 : 03:34:10

Also search for where in @MYCSV here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

bapugandu77
Starting Member

2 Posts

Posted - 2006-04-13 : 16:40:55

Thank you for your help everyone. I think rockmoose nailed it pretty well.

I think if we could generate crosstab reports in SQL Server,
it could have be done pretty easily. Any thoughts?

Thanks again

k

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-14 : 04:56:18

quote:
Originally posted by bapugandu77

Thank you for your help everyone. I think rockmoose nailed it pretty well.

I think if we could generate crosstab reports in SQL Server,
it could have be done pretty easily. Any thoughts?

Thanks again

k

It's an idea.
You could implement a crosstab view, and select from that.
Specially if the set of SkillID is rather fixed.

create table #CS(CndID int, SkillID int)
insert #CS
select 1, 4
union all select 1 , 6
union all select 2 , 8
union all select 2 , 4
union all select 2 , 10
union all select 3 , 4
union all select 3 , 8


select	* from
(
        /*This could be a view*/
	select	CndID
		,max(case when SkillID = 4 then 1 else 0 end) as skill4
		,max(case when SkillID = 6 then 1 else 0 end) as skill6
		,max(case when SkillID = 8 then 1 else 0 end) as skill8
		,max(case when SkillID = 10 then 1 else 0 end) as skill10
	from	#CS
	group by
		CndID
) crosstab
where	skill8 = 1
	and skill10 = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-14 : 06:15:00

Refer this also
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources