Re: Query problem - Possible solution - Mailing list pgsql-novice

--- operationsengineer1@yahoo.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