query help - 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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

h27091978
Starting Member

4 Posts

Posted - 2006-07-25 : 02:23:52

I have a table as:
tableA
col1 col2

1 4
1 5
1 20
1 8
2 4
2 21
3 8
3 5

now i want to write a query to get the record where all the values in col2 match like
if i send col2=4,5,20,8 the query should return me col1=1

if i write a query using the IN clause it returns me all the records matching to col2=4,5,20 and 8

please help.

thanks a lot for your time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-25 : 02:32:14

[code]select distinct col1
from tableA a
where exists (select * from tableA x where x.col1 = a.col1 and x.col2 = 4)
and exists (select * from tableA x where x.col1 = a.col1 and x.col2 = 5)
and exists (select * from tableA x where x.col1 = a.col1 and x.col2 = 8)
and exists (select * from tableA x where x.col1 = a.col1 and x.col2 = 20)[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-25 : 02:40:31


Declare @var Table
(
Col1 int, 
Col2 int 
)
Insert @var 
Select 1, 4 Union All 
Select 1, 5 Union All 
Select 1, 20 Union All 
Select 1, 8 Union All 
Select 2, 4 Union All 
Select 2, 21 Union All 
Select 3, 8 Union All 
Select 3, 5

Select Distinct Col1 From @var Where Col2 In (4,5,20,8)

 

Select Col1 From @var Where Col2 In (4,5,20,8) Group by Col1

Chirag

h27091978
Starting Member

4 Posts

Posted - 2006-07-25 : 02:52:47

Thanks for the quick reply khtan..
the query that i had formed was this:
select col1 from tableA where col1 in (select col1 from tableA where col2=4) and
col1 in (select col1 from tableA where col2=5) and
col1 in (select col1 from tableA where col2=20) and
col1 in (select col1 from tableA where col2=8)

but the problem is that the col2 values are dynamic.. meaning in this case its 4,5,20,8
this number of parameters may vary...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-25 : 03:09:53

Did you tried out the query which i posted?

Chirag

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 07:40:36

How about this? The input is dynamic, and it returns any group which contains all integers in the input string (I understood that to be what you wanted).

--data
declare @t table (col1 int, col2 int)
insert @t 
          select 1, 4
union all select 1, 5
union all select 1, 20
union all select 1, 8
union all select 2, 4
union all select 2, 21
union all select 3, 8
union all select 3, 5

--inputs
declare @s varchar(100)
set @s = '4,5,20,8'
--set @s = '4,21' --returns 2
--set @s = '5,8' --returns 1 and 3, is this what we want?

--calculation
select col1 from @t where col2 in (select Data from dbo.Split(@s, ','))
group by col1
having count(*) = (len(@s) - len(replace(@s, ',', '')) + 1)

/*results
col1        
----------- 
1
*/

dbo.Split can be found here (or you may have your own):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 07:54:37

This version will return only 3 for "5,8", rather than 1 and 3. Take your pick according to what you need.

select col1 from @t t left outer join (select data from dbo.Split(@s, ',')) a on a.data = t.col2
group by col1
having count(*) = (len(@s) - len(replace(@s, ',', '')) + 1) and count(data) = count(col1)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

h27091978
Starting Member

4 Posts

Posted - 2006-07-25 : 09:21:23

thanks a lot Ryan. I think second one will work for me... i'll try and let you know.
THanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources