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,
LEFT JOIN t_link_contract_number_job_number
LEFT JOIN t_job_number
LEFT JOIN t_product
ON ( t_product.product_id =
LEFT JOIN t_inspect
ON ( t_serial_number.serial_number_id =
LEFT JOIN t_inspect_area
ON ( t_inspect.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.
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
pgsql-novice by date
|Next:||From: operationsengineer1||Date: 2006-01-16 19:46:39|
|Subject: Re: Query problem - Possible solution|
|Previous:||From: Andreas Kretschmer||Date: 2006-01-16 16:46:08|
|Subject: Re: Help with query|