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