query help

[prodadmin]

Hi

customerid number(6)
customertype char(1)


we have two different customers types(P,A).Some are only one customer type but some have both types.

customerid -- ctype
----------- -------
123 -p
234-A
234-P
345-A
888-A

how do i retreive only those who has both customertype values such as 234

Thanks

[LKBrwn_DBA]

Maybe you could use the COUNT() function with the SELECT ... HAVING option. ;)

[prodadmin]

i did , still wrong ,here is the query

select cid, ctype, count(*) from cust
and ctype in ( 'P', 'A') group by cid, ctype
having ctype in ( 'P', 'A')

[tamilselvan]

SQL> select * from cust ;

ID T
---------- -
123 P
234 A
234 P
345 A
888 A

SQL> get x1
1 select a.id
2 from cust a, cust b
3 where a.id = b.id and
4 a.type = 'A' and
5* b.type = 'P'
SQL> /

ID
----------
234



Tamil

[WilliamR]

i did , still wrong ,here is the query

select cid, ctype, count(*) from cust
and ctype in ( 'P', 'A') group by cid, ctype
having ctype in ( 'P', 'A')

LKB was probably thinking of something more like:

SQL> select * from c;

CUSTOMERID C
---------- -
123 P
234 A
234 P
345 A
888 A

5 rows selected.

SQL> SELECT customerid, COUNT(*)
2 FROM c
3 GROUP BY customerid
4 HAVING MIN(customertype) <> MAX(customertype);

CUSTOMERID COUNT(*)
---------- ----------
234 2

1 row selected.

[prodadmin]

thanks , both works for me .much appreciated