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.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
