Basic Sql Question

[Nift]

I'm new to SQL and I'm stuck on a problem I'm trying to do in Oracle. I have two tables CUSTOMERS and ORDERS as follows:

CUSTOMERS+--------+---------+|   cid  |   cname |	 +--------+---------+|   c001  | TipTop | |   c002  | Basics | |   c003  | Allied |	   |   c004  | ACME   |	  |   c006  | ACME   |		+--------+---------+  ORDERS+-------+------+-----+-----+|ordnum | cid  | aid | pid | +-------+------+-----+-----||   1011| c001 | a01 | p01 ||   1012| c001 | a01 | p01 ||   1019| c001 | a02 | p02 ||   1017| c001 | a06 | p03 ||   1021| c004 | a06 | p01 ||   1016| c006 | a01 | p01 ||   1024| c006 | a06 | p01 |+-------+------+-----+-----+

I'm trying to figure out an SQL command that can list the cid of customers who are linked to the pid values 'p01' AND 'p02'. So for example, 'c001' should come up since it is linked to both 'p01' and 'p02'. I tried this:

SQL> SELECT customers.cid  FROM customers JOIN orders ON customers.cid = orders.cid WHERE  pid='p01' AND pid='p02';

...before I realized that the logic behind that statement doesn't make sense. I've been racking my brain for a couple of hours now trying to figure it out. It's frustrating because I feel like the solution is simple but I'm just missing it.I would greatly appreciate any help.

[justsomeguy]

Try an intersect:SELECT cid FROM orders WHERE pid='p01'INTERSECTSELECT cid FROM orders WHERE pid='p02'

[Hillel]

Hello.For you it is not the valid SQL query (WHERE pid='p01' AND pid='p02'):

SQL> SELECT customers.cid  FROM customers JOIN orders ON customers.cid = orders.cid WHERE  pid='p01' AND pid='p02';

And it is the valid SQL query (WHERE pid='p01' OR pid='p02'):

SQL> SELECT customers.cid  FROM customers JOIN orders ON customers.cid = orders.cid WHERE  pid='p01' OR pid='p02';

Thanks.

[Synook]

Won't that just select records which have either p01 or p02?

[Hillel]

Won't that just select records which have either p01 or p02?

Hello, Synook.I don`t know what exactly wants Nift...But this query:

SQL> SELECT c.cid, o.pidFROM customers AS cJOIN orders AS oON c.cid = o.cid WHERE  o.pid='p01' OR o.pid='p02';

Gives this result:

+-------+-------+| c.cid | o.pid |+-------+-------+|  c001 |   p01 ||  c001 |   p01 ||  c001 |   p02 ||  c004 |   p01 ||  c006 |   p01 ||  c006 |   p01 |+-------+-------+

It is possible to use here "GROUP BY" or "DISTINCT" depending on concrete requirement.Thanks.

[Nift]

Try an intersect:SELECT cid FROM orders WHERE pid='p01'INTERSECTSELECT cid FROM orders WHERE pid='p02'

Ah, that did it. Thanks!