Posted by gelangov on 07/23/07 17:33

On Jul 23, 11:32 am, Roy Harvey <roy_har...@snet.net> wrote:
> --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, gelan...@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 !!!- Hide quoted text -
>
> - Show quoted text -

Thank you!!!

[Back to original message]