Re: Help needed with complex Query
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
Re: Help needed with complex Query
Philip Lawatsch wrote: 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: SELECT widgets.widget-id, number_of_parts, count(partid) AS cnt FROM widgets INNER JOIN part_mapping ON widgets.widget-id = part_mapping.widget-id GROUP BY widgets.widget-id, number_of_parts HAVING cnt = number_of_parts; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
Unless you are keeping track of whether a widget in complete or not, there is no hope but to scan the entire table to determine if a widget is complete. That's something you don't want to do. You should mark a widget as complete when it is completed. This would mean checking if a particular widget is completed when a modification occurs. Really what you are going to be doing is running the loop you spoke of in bits and pieces of time and only for widgets that require checking/updating. Then you only need to query on the completed field, which will be very fast. To do it in a single query, you going to need to use count and group by and then check for completion using HAVING (as opposed to where). On Jun 6, 2005, at 1:37 PM, Philip Lawatsch wrote: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
[EMAIL PROTECTED] wrote: 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: snip 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. Thanks a lot, this did the trick! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
