Posted by Roy Harvey on 07/23/07 15:32

--Query 1
SELECT field1
FROM Table1
GROUP BY field1
HAVING COUNT(distinct field2) = 2
AND COUNT(distinct CASE WHEN field2 IN ('a', 'b')
THEN field2
END) = 2

--Query 2
SELECT field1
FROM Table1
WHERE field2 IN ('a', 'b')
GROUP BY field1
HAVING COUNT(distinct field2) = 2

Roy Harvey
Beacon Falls, CT

On Mon, 23 Jul 2007 08:00:23 -0700, gelangov@hotmail.com wrote:

>create table table1
>(field1 varchar (8),
>field2 varchar (4))
>
>field1 and field2 together is unique.
>
>Example data:
>
>Field1 Field2
>1 A
>1 B
>1 C
>2 A
>2 C
>3 A
>3 B
>3 D
>4 A
>4 B
>
>1. The query one should find all the Field1 that has Field2 value of
>ONLY (A) and (B)
>Here the result will be Field1, 4 (only one result)
>
>2. The query two should find all the Field1 that has Field2 value of
>(A)and (B)
>Here the result will be Field1,1,3 and 4 (three results)
>
>Right now, I am doing this way...Is there a more efficient way than
>this?
>
>
>-- Query one:
>
>
>SELECT <a.field1> from
>
>(SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='A'
>
>UNION
>
>SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='B'
>
>UNION
>
>SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='C') a
>
>WHERE <a.field1> not in (SELECT <field1>
>
>FROM table1 WHERE <field2> not in ('a', 'b', 'c'))
>
>GROUP by <a.field1>
>
>HAVING count(*) =3
>
>order by <a.field1>
>
>
>--query 2
>
>SELECT <a.field1> from
>
>(SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='a'
>
>UNION
>
>SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='b'
>
>UNION
>
>SELECT <field1>, <field2> FROM table1
>
>WHERE <field2> ='c'
>
>)a
>
>GROUP by <a.field1>
>
>HAVING count(*) =3
>
>order by <a.field1>
>
>
>
>Thank you so much !!!

[Back to original message]