×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Division by comulm

Division by comulm

Division by comulm

(OP)
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?

RE: Division by comulm

Well, the answer is quite simple:

select o_id from orderline group by id having pid=6

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources