Re: [sqlite] SQL help

[Dennis Cote]

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]
-----------------------------------------------------------------------------