Jump to content

Basic Sql Question


Nift
 Share

Recommended Posts

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.

Edited by Nift
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...