>You could try this: > select count(*) as mcount, sum(is_complete) as mcomplete > from tech_modules > where tech_id = ? and coll_id = ?;
Dennis, Yeah, that's much cleaner. Just once through the tech's module set instead of twice, and it satisfies both requirements (at least some modules, and all complete). Thanks again! -Clark ----- Original Message ---- From: Dennis Cote <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, April 3, 2007 12:27:54 PM Subject: Re: [sqlite] SQL help Clark Christensen wrote: > I have a table, as described below, where I need to find out if the tech_id > in question has at least some modules in a particular collection (coll_id), > and they're all complete. > > At this point, I'm working with variations on: > > select > (select count(*) from tech_modules > where tech_id = ? and coll_id = ?) as mcount, > (select count(*) from tech_modules where tech_id = ? > and coll_id = ? and is_complete = 1) as mcomplete; > > Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && > $mcomplete == $mcount) to get a boolean $allComplete. > > It performs OK (~50K rows in the table), using the unique index for both > subqueries, but the SQL seems crude. Anybody have a more elegant solution to > share? > > Thanks! > > -Clark > > CREATE TABLE TECH_MODULES ( > TECH_ID integer, > MODULE_ID integer, > COLL_ID integer, > IS_COMPLETE integer default 0, > COMPLETION_TIME date, > COMPLETION_TARGET date, > DELETED integer > ); > > CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id); > > CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, > coll_id, module_id); > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > Clark, You could try this: select count(*) as mcount, sum(is_complete) as mcomplete from tech_modules where tech_id = ? and coll_id = ?; HTH Dennis Cote ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
