Re: Query problem - Possible solution

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