Skip site navigation (1) Skip section navigation (2)

Re: Query problem - Possible solution

From: <operationsengineer1(at)yahoo(dot)com>
To: operationsengineer1(at)yahoo(dot)com, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Query problem - Possible solution
Date: 2006-01-16 19:46:39
Message-ID: 20060116194639.56451.qmail@web33304.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- operationsengineer1(at)yahoo(dot)com wrote:

> while working with and testing my new favorite
> query,
> i ran into some behavior that i didn't expect.
> 
> the query is as follows:
> 
> SELECT DISTINCT t_product.product_id,
>        t_product.product_number,
>        t_serial_number.serial_number_id,
>        t_serial_number.serial_number::int
> FROM t_serial_number
> LEFT JOIN t_link_contract_number_job_number
>     ON (
> t_serial_number.link_contract_number_job_number_id =
>         
>
t_link_contract_number_job_number.link_contract_number_job_number_id
>        )
> LEFT JOIN t_job_number
>    ON (
> t_link_contract_number_job_number.job_number_id =
>         t_job_number.job_number_id
>       )
> LEFT JOIN t_product
>    ON ( t_product.product_id =
>         t_job_number.product_id
>       )
> LEFT JOIN t_inspect
>    ON ( t_serial_number.serial_number_id =
>         t_inspect.serial_number_id
>       )
> LEFT JOIN t_inspect_area
>    ON ( t_inspect.inspect_area_id =
>         t_inspect_area.inspect_area_id
>       )
> WHERE t_serial_number.serial_number_id NOT IN
>       (SELECT serial_number_id FROM t_inspect
>        WHERE t_inspect_area.inspect_area_id = 1
>        AND inspect_pass = true)
> ORDER BY t_serial_number.serial_number::int ASC
> 
> my purpose is to find the serial number that doesn't
> have at least one TRUE inspect_pass value by serial
> number / inspect area.
> 
> starting with no data:  i enter in any number of
> production inspect (t_inspect_area.inspect_area_id =
> 1) fails for serial number 1 (s/n 1).  when i query
> inspect_id = 1, s/n 1 returned as expected.  i enter
> a
> true value for t_inspect_area.inspect_area_id = 1
> and
> s/n 1 no longer returns when i query inspect_area_id
> =
> 1 - just as expected.
> 
> the problem begins when i move to qa inspection
> (t_inspect_area.inspect_area_id = 2) and enter a
> true
> value for s/n 1.
> 
> not only does a query for inspect_id = 2 (qa
> inspect_area) return s/n 1, now a query based on
> inspect_id returns s/n 1.
> 
> i'm stumped as to why.  any help would be
> appreciated.
> 
> __________________________________________________

adding a left join on my where clause seems to have
done the trick.  the where clause now looks as
follows:

WHERE t_serial_number.serial_number_id NOT IN
      (SELECT serial_number_id 
        FROM t_inspect
        LEFT JOIN t_inspect_area
        ON ( t_inspect.inspect_area_id = 
           t_inspect_area.inspect_area_id
            )
       WHERE t_inspect_area.inspect_area_id = 2
       AND inspect_pass = true)

i'll keep testing, though.  ;-)

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

In response to

pgsql-novice by date

Next:From: Minal A. AryamaneDate: 2006-01-17 08:39:59
Subject: Fw: Help needed
Previous:From: operationsengineer1Date: 2006-01-16 19:26:25
Subject: Query problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group