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

-- Query 1
select Field1
from table1
group by Field1
having sum(case when Field2 not in ('A','B') then 1 else 0 end)=0

-- Query 2
select Field1
from table1
group by Field1
having sum(case when Field2='A' then 1 else 0 end)>0
and sum(case when Field2='B' then 1 else 0 end)>0

--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 (AT) hotmail (DOT) com wrote:

On Jul 23, 11:32 am, Roy Harvey <roy_har... (AT) snet (DOT) net> wrote:
Thank you!!!