Thread: SQL Question

and another sql question...

greatly simplified info:

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

iow, a sn may have 1 inspection - t or 4 inspections -
f, f, f, t or any number of inspections as long as
they are 1. all f or 2. they are f up until the final
t value.

in php, i'd run a select where inspect_pass = true and
test to see if the recordset contained a value or not.

in this case, it is required that sql itself generate
the correct result, if possible.

tia...

my app actually has about 6 distinct test areas, each
requiring this test, but i thought i'd KISS it so the
request is clear.  eventually, i will want to test
that all prior inspections ended in t before allowing
the user to input the current inspection...

app minutae can wear a guy down!



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

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 Fuhr

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

--- Stephen Clouse <stephenclouse@gmail.com> wrote:

> On 1/6/06, operationsengineer1@yahoo.com
> <operationsengineer1@yahoo.com>
> wrote:
> >
> > and another sql question...
> >
> > greatly simplified info:
> >
> > 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
>
>
> This should get you what you're looking for:
>
> SELECT sn FROM t_sn
> WHERE sn_id NOT IN
>     (SELECT sn_id FROM t_inspect
>      WHERE inspect_pass = t)

perfect!  thank you Stephen!  so concise, too.
beautiful!

i was misapplying "not exists" and it was a mess.  i
had it displaying all or none of the sns.

my last hurdle is to make my query also sort by
inspection area...  and i'll attack that tomorrow.

thanks again.




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

Hello,

We're using PostgreSQL 8.1 on OS X Server. If we have auto vacuum
configured, does that mean we don't have to run analyze on our
tables? Does auto vacuum handle that for us? Or should we still
create a cron job to run analyze at a regularly scheduled interval?

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

Brendan Duddridge <brendan@clickspace.com> writes:
> We're using PostgreSQL 8.1 on OS X Server. If we have auto vacuum
> configured, does that mean we don't have to run analyze on our
> tables? Does auto vacuum handle that for us?

Yes, though you might wish to twiddle its settings for how often to
do it.

            regards, tom lane