Philip Lawatsch <[EMAIL PROTECTED]> wrote on 06/06/2005 01:37:37 PM:
> Hi,
> I'm trying hard to figure out how to perform a special query in mysql
4.0.
> I have one table "widgets" which has a column "widget-id" (int) and one
> column "number_of_parts" (int).
> And then I have another table "part_mapping" which has one column
> "widget-id" (int) and one column "part_id" (int).
> part_id is unique throughout the "part_mapping" table.
> The idea is that every widget consists of several unique parts.
> Now I want to select all widgets which are complete, this means where
> SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the
> number_of_parts of "widget-id" in table widgets.
> What I could do is simply "loop" over table "widgets" and execute a
> select count for every wiget. This would result in a huge number if
> queries needed form my client which is something I'd like to avoid.
> I pretty much have no idea how I can do this without nested queries (and
> to be frank not even how to do it with them) so I'd really appreciate
> any help!
> kind regards Philip
Try this as a starting point:
SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2;
Wherever the column `number_of_parts` equals the computed value of
part_count, you have a complete widget. Here is a query that returns only
completed widgets:
SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `number_of_parts` = `part_count`;
Here is one that returns incomplete widgets:
SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `number_of_parts` > `part_count`;
Here is the query that tell you that construction on these widgets hasn't
even started:
SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `part_count` = 0;
I think you were having two mental problems: 1) how to GROUP BY across
tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer
To the Fine Manual (RTFM) you can get examples and more explanations of
both processes.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
