they query i'm using is as follows:
SELECT t_product.product_id,
t_product.product_number,
t_serial_number.serial_number_id,
t_serial_number.serial_number,
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 = 2
AND inspect_pass = true)
OR t_inspect_area.inspect_area_id IS NULL
ORDER BY serial_number::int ASC
my last problem is that serial number repeats for each
inspection. let's say 2/n has four fails w/o a pass.
it will display four rimes. i want it to display a
single time. select distinct didn't work. i don't
know if it is possible to get distinct values withing
an ON clause.
i need to check all 4 inspections (for same serial
number) to see if one of them is a pass, but i only
want to display a single serial number if there is no
pass (or if it is null - inspection not completed
yet).
tia...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
