Posted by gelangov on 07/23/07 15:00

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]