Helpp - 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

Helpp

Author

Topic

asilazmaz
Starting Member

2 Posts

Posted - 2007-07-24 : 04:13:11

I have a table that contains two column as below
A B
1 0
1 1
1 2
1 3
2 0
2 1
2 5
3 0
3 3
3 7

I want to select A which B columns include 0 and 1 so return value must be 1 and 2. how can i write sql command?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-24 : 04:16:34

Select A from tablename
where B in (0,1)
group by A
having count(*) = 2

--------------------------------------------------
S.Ahamed

asilazmaz
Starting Member

2 Posts

Posted - 2007-07-24 : 04:32:14

Ok, what if my searched B counts is not known. For example, in one statement i want to find B = (0,1) and in other i want to find B = (0,1,2,4). In all condition ,count(*) = 2 must be changed. I ask it becase the search parameters (0,1) are taken from user. But i really thank you Ahamed...

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-24 : 04:51:02

Insert the user specified values to a table variable and do the join as below....

declare @t table (A int, B int)
insert @t
select 1, 0 union all
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 0 union all
select 2, 1 union all
select 2, 5 union all
select 3, 0 union all
select 3, 3 union all
select 3, 7

declare @y table(y int)
Insert @y
select 0 union
select 1 union
select 2

Select A
From @t a join @y b
on a.B = b.Y
group by A
having count(*) = (Select count(*) from @y)

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:33:34

HAVING COUNT(DISTINCT B) = (SELECT COUNT(*) FROM @y)

Just in case there are duplicate values in the @t table.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources