criteria in group - need help to improve code

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

Transact-SQL (2000)

criteria in group - need help to improve code

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2009-05-11 : 16:46:57

declare @tbl1 table (id int, base char(1))
insert into @tbl1 (id, base)
select 1, 'a'
union all
select 1, 'b'
union all
select 2, 'a'
union all
select 2, 'c'
union all
select 3, 'b'
union all
select 3, 'a'

select a.id from @tbl1 a
inner join
(
select id, count(id) bcount from @tbl1
where base in ('b') group by id)b
on a.id=b.id
where bcount > 0 and base in ('a')
group by a.id
having count(a.id) > 0

With the sample above, can this be rewritten differently? I trying to find which id have both 'A' and 'B' as the based.

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-11 : 17:09:27

You could do this:

SELECT a.id
FROM  @tbl1 AS A
INNER JOIN
@tbl1 AS B
ON A.id = B.id
AND A.base = 'a'
AND B.base = 'b'

If you have a case where you would have one ID with multiple a's and b's, you could add a DISTINCT to the SELECT clause.

hai
Yak Posting Veteran

84 Posts

Posted - 2009-05-12 : 08:53:49

Thank you very much...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 09:26:24

or

select id from @tbl1
group by id
having sum(case when base='a' then 1 else 0 end+case when base='b' then 1 else 0 end)=2

Madhivanan

Failing to plan is Planning to fail

hai
Yak Posting Veteran

84 Posts

Posted - 2009-05-12 : 13:01:33

declare @tbl1 table (id int, base char(1))
insert into @tbl1 (id, base)
select 1, 'a'
union all
select 1, 'b'
union all
select 1, 'b'
union all
select 2, 'a'
union all
select 2, 'c'
union all
select 3, 'b'
union all
select 3, 'a'
union all
select 4, 'b'
union all
select 4, 'b'

SELECT distinct a.id
FROM @tbl1 AS A
INNER JOIN
@tbl1 AS B
ON A.id = B.id
AND A.base = 'a'
AND B.base = 'b'

select id from @tbl1
group by id
having sum(case when base='a' then 1 else 0 end+case when base='b' then 1 else 0 end)=2

your code doesn't work Madhivanan.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 01:50:35

Ok. It should then be

select id from @tbl1
where base in ('a','b')
group by id
having count(distinct(base))=2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources