Same field different values

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 from

(SELECT , FROM table1

WHERE ='A'

UNION

SELECT , FROM table1

WHERE ='B'

UNION

SELECT , FROM table1

WHERE ='C') a

WHERE not in (SELECT

FROM table1 WHERE not in ('a', 'b', 'c'))

GROUP by

HAVING count(*) =3

order by


--query 2

SELECT from

(SELECT , FROM table1

WHERE ='a'

UNION

SELECT , FROM table1

WHERE ='b'

UNION

SELECT , FROM table1

WHERE ='c'

)a

GROUP by

HAVING count(*) =3

order by



Thank you so much !!!

Re: Same field different values

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

Re: Same field different values

--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 from
>
>(SELECT , FROM table1
>
>WHERE ='A'
>
>UNION
>
>SELECT , FROM table1
>
>WHERE ='B'
>
>UNION
>
>SELECT , FROM table1
>
>WHERE ='C') a
>
>WHERE not in (SELECT
>
>FROM table1 WHERE not in ('a', 'b', 'c'))
>
>GROUP by
>
>HAVING count(*) =3
>
>order by
>
>
>--query 2
>
>SELECT from
>
>(SELECT , FROM table1
>
>WHERE ='a'
>
>UNION
>
>SELECT , FROM table1
>
>WHERE ='b'
>
>UNION
>
>SELECT , FROM table1
>
>WHERE ='c'
>
>)a
>
>GROUP by
>
>HAVING count(*) =3
>
>order by
>
>
>
>Thank you so much !!!

Re: Same field different values

On Jul 23, 11:32 am, Roy Harvey 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 from
>
> >(SELECT , FROM table1
>
> >WHERE ='A'
>
> >UNION
>
> >SELECT , FROM table1
>
> >WHERE ='B'
>
> >UNION
>
> >SELECT , FROM table1
>
> >WHERE ='C') a
>
> >WHERE not in (SELECT
>
> >FROM table1 WHERE not in ('a', 'b', 'c'))
>
> >GROUP by
>
> >HAVING count(*) =3
>
> >order by
>
> >--query 2
>
> >SELECT from
>
> >(SELECT , FROM table1
>
> >WHERE ='a'
>
> >UNION
>
> >SELECT , FROM table1
>
> >WHERE ='b'
>
> >UNION
>
> >SELECT , FROM table1
>
> >WHERE ='c'
>
> >)a
>
> >GROUP by
>
> >HAVING count(*) =3
>
> >order by
>
> >Thank you so much !!!- Hide quoted text -
>
> - Show quoted text -

Thank you!!!