Re: SQL Question

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Fri, Jan 06, 2006 at 04:02:53PM -0800,
> operationsengineer1@yahoo.com wrote:
> > t_sn
> > sn_id
> > sn
> >
> > t_inspect
> > inspect_id
> > sn_id (fkey)
> > inspect_pass (boolean)
> >
> > i want to display all sns where there is not a
> single
> > instance of inspect_pass = t
>
> There are several ways to do this, e.g., a query
> with NOT IN or a
> join with a COUNT or SUM aggregate and a HAVING
> clause.  What have
> you tried so far?
>

Michael, thanks for the response.  i spent some more
time on this and i think i found part of the solution.

i tried adding

WHERE NOT EXISTS(SELECT t_inspect.inspect_id FROM
t_inspect WHERE t_inspect.inspect_pass = true)

to my series of left joins that left me with total
serial numbers associated with a product number (i
want a subset of this total where
t_inspect.inspect_pass = true).  unfortunately, this
worked on the aggregate - it displays *all* or none,
with no inbetween.

i tried "not in", but that kicked out an error (i
probably misapplied it).  exists and not exists seemed
to work in similar fashion, but they display the
entire set instead of the desired subset.

i'll read up on



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com