Re: Sort of Complex Query - Howto Eliminate Repeating Results

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

the problem appears to be here:

LEFT JOIN t_inspect
ON ( t_serial_number.serial_number_id =
t_inspect.serial_number_id

this includes every single inspection in the resulting
table, whereas, i only want to list 1 as long as 1 or
more exist. i googled and didn't find any results. i
google rouped - no results.

i tried distinct, limit, group by in various
locations. no luck.

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com