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