Division by comulm

[shabKOOL]

Hi everyone,

Two tables


Parts - table

+-----+---------+--------+--------+
| pid | name | colour | weight |
+-----+---------+--------+--------+
| 1 | widget | red | 10 |
| 2 | loofa | puce | 20 |
| 3 | pike | blue | 30 |
| 4 | hingie | green | 25 |
| 5 | whatsit| red | 15 |
| 6 | oobar | green | 20 |
+-----+---------+--------+--------+
6 rows in set (0.24 sec)

orderline - table
+------+-----+------+
| o_id | pid | qty |
+------+-----+------+
| 1 | 1 | 100 |
| 1 | 2 | 80 |
| 1 | 3 | 70 |
| 1 | 6 | 80 |
| 2 | 1 | 90 |
| 2 | 2 | 60 |
| 2 | 3 | 50 |
| 2 | 4 | 60 |
| 2 | 5 | 10 |
| 2 | 6 | 20 |
| 3 | 1 | 10 |
| 3 | 2 | 10 |
| 3 | 5 | 10 |
| 4 | 1 | 40 |
| 4 | 3 | 10 |
| 5 | 1 | 50 |
| 5 | 2 | 80 |
| 5 | 3 | 45 |
| 5 | 4 | 60 |
| 5 | 5 | 70 |
| 5 | 6 | 60 |
| 6 | 5 | 10 |
| 7 | 4 | 20 |
| 7 | 6 | 30 |
| 8 | 3 | 25 |
| 9 | 5 | 10 |
| 10 | 2 | 20 |
| 10 | 4 | 20 |
| 10 | 5 | 50 |
| 10 | 6 | 20 |
| 11 | 1 | 20 |
| 11 | 2 | 10 |
| 11 | 4 | 50 |
| 11 | 6 | 25 |
+------+-----+------+
34 rows in set (0.07 sec)



I want to find out which o_id (orders) have ordered all the parts. I am trying to do this by division but I get the wrong answer.

I am dividing orderline.pid / parts.pid

mysql> SELECT DISTINCT orderline.o_id
mysql> FROM parts,orderline
mysql> WHERE orderline.pid / parts.pid


results:

| o_id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+------ +
11 rows in set (0.04 sec)

I should really get O_ID = 2 , 5.

Any1 help me?

 

[rtmpxr]

Well, the answer is quite simple:

select o_id from orderline group by id having pid=6