Fileter

8 Jul 2005 2:07 PM

hals_left

Hi,
I am trying to return a set of identifiers where each exists exactly
twice in a larger data set (rule 1) and where the corresponsing values
for each id all belong to another set (rule 2).

For the following example data I want to return ids that appear in two
rows and whose values are in the set (A,C) . So the correct query
should return (2).

The problem I a having is that if I count and group by id fto determine
rule 1 I only get one value returned so cant test rule 2.

Hopefully that makes sense!
Thanks

id   value
1    A
1    B
2    A
2    C
3    A
4    A

8 Jul 2005 3:33 PM

Alejandro Mesa

Try,

use tempdb
go

create table t1 (
[id] int,
value char(1)
)
go

insert into t1 values(1, 'A')
insert into t1 values(1, 'B')
insert into t1 values(2, 'A')
insert into t1 values(2, 'C')
insert into t1 values(3, 'A')
insert into t1 values(4, 'A')
go

select
    [id]
from
    t1 as a
where
    (value = 'A' or value = 'C')
    and exists(
    select
        *
    from
        t1 as b
        inner join
        t1 as c
        on b.[id] != a.[id]
        and c.[id] = a.[id]
        and b.value = c.value
    )
group by
    [id]
having
    min(value) != max(value)
go

drop table t1
go

AMB

Show quote

"hals_left" wrote:

> Hi,
> I am trying to return a set of identifiers where each exists exactly
> twice in a larger data set (rule 1) and where the corresponsing values
> for each id all belong to another set (rule 2).
>
>
>
> For the following example data I want to return ids that appear in two
> rows and whose values are in the set (A,C) . So the correct query
> should return (2).
>
> The problem I a having is that if I count and group by id fto determine
> rule 1 I only get one value returned so cant test rule 2.
>
> Hopefully that makes sense!
> Thanks
>
>
> id   value
> 1    A
> 1    B
> 2    A
> 2    C
> 3    A
> 4    A
>
>