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]
