Need help with alternate solution to "intersect" in a single table... 2

[xpmjp]

Hello. I am having a problem getting my desired outcome with my table. Here is what I have.

Table: orders

+-------+-------+------+------+------+------+---------+
| ordno | month | cid | aid | pid | qty | dollars |
+-------+-------+------+------+------+------+---------+
| 1011 | jan | c001 | a01 | p01 | 1000 | 450 |
| 1012 | jan | c001 | a01 | p01 | 1000 | 450 |
| 1019 | feb | c001 | a02 | p02 | 400 | 180 |
| 1017 | feb | c001 | a06 | p03 | 600 | 540 |
| 1018 | feb | c001 | a03 | p04 | 600 | 540 |
| 1023 | mar | c001 | a04 | p05 | 500 | 450 |
| 1022 | mar | c001 | a05 | p06 | 400 | 720 |
| 1025 | apr | c001 | a05 | p07 | 800 | 720 |
| 1013 | jan | c002 | a03 | p03 | 1000 | 880 |
| 1026 | may | c002 | a05 | p03 | 800 | 704 |
| 1015 | jan | c003 | a03 | p05 | 1200 | 1104 |
| 1014 | jan | c003 | a03 | p05 | 1200 | 1104 |
| 1021 | feb | c004 | a06 | p01 | 1000 | 460 |
| 1016 | jan | c006 | a01 | p01 | 1000 | 500 |
| 1020 | feb | c006 | a03 | p07 | 600 | 600 |
| 1024 | mar | c006 | a06 | p01 | 800 | 400 |
+-------+-------+------+------+------+------+---------+

What I am trying to do is list only the entries from cid that have both 'p01' and 'p02' in the pid column.

In oracle(sqlplus) I did it like this:

select cid from orders where pid='p01'
intersect select cid from orders where pid='p02';

Thank you.

 

[xpmjp]

Sorry, I forgot to include my last attempt of code:

SELECT cid from orders
WHERE (pid='p01' AND pid='p02') group by cid;

 

[TonyGroves]

You could use something like:
[tt]
SELECT cid,GROUP_CONCAT(pid) gc
FROM orders
GROUP BY cid
HAVING
FIND_IN_SET('p01',gc) AND FIND_IN_SET('p02',gc)
[/tt]

 

[xpmjp]

Thanks Tony!

Few questions. Can you explain to me the reasoning behind group_concat(pid) gc ?

And with that code it displays both the cid and gc (pids):

+------+---------------------------------+
| cid | gc |
+------+---------------------------------+
| c001 | p01,p01,p02,p03,p04,p05,p06,p07 |
+------+---------------------------------+

How would you get it to just display the cid?

Thanks again!

 

[TonyGroves]

To show only cid, you could use a subquery:
[tt]
SELECT gc
FROM
(
SELECT ...
)
[/tt]
The GROUP_CONCAT is quite simple; for each cid, it returns all the pid's in one comma-delimited string. Then, you can pick out those records whose group-concat string contains both 'p01' and 'p02'.

 

[TonyGroves]

Sorry, that should of course have been:
[tt]
SELECT cid
FROM
(
SELECT ...
)
[/tt]

 

[r937]

What I am trying to do is list only the entries from cid that have both 'p01' and 'p02' in the pid column

select cid
  from orders
 where pid in ('p01','p02')
group
    by cid
having count(*) = 2

r937.com | rudy.ca

 

[xpmjp]

Thanks guys!

 

[TonyGroves]

Rudy:
Nice idea, but won't that also catch those 'cid's which have, for example, 2 'p01's but no 'p02's?

xpmjp:
If you try my solution, you will get a syntax error complaining about the lack of a subquery alias. Silly, but true. To fix, this add any name (sq for example) to the end of the query.

 

[r937]

tony, yes, that's right, and if that's a concern, you would use HAVING COUNT(DISTINCT pid)=2

r937.com | rudy.ca