Re: Sort of Complex Query - Howto Eliminate Repeating Results

> On Thu, Jan 12, 2006 at 09:08:59AM -0800,
> operationsengineer1(at)yahoo(dot)com wrote:
> > 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
>
> Are you sure this is the query you're using? It has
> a syntax error
> due to the comma after the final field in the select
> list. As
> written this query shouldn't run at all, so it's
> hard for us to
> trust that it's what you're really doing.

i deleted a 5th select result in yahoo w/o deleting
the comma - whoops! otherwise it is 100% what i'm
using. oh, and you are right - it will not work with
the extra comma.

> > 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.
>
> How didn't SELECT DISTINCT work? Did it return the
> wrong results?
> Did it fail with a syntax error? If you got an
> error like
>
> ERROR: for SELECT DISTINCT, ORDER BY expressions
> must appear in select list
>
> then try qualifying serial_number in the ORDER BY
> clause, like this:
>
> ORDER BY t_serial_number.serial_number::int ASC;
>
> --
> Michael Fuhr

Michael, i did as you said. i've posted the select
statement (exactly, this time!) and the resulting
error.

select statement:

SELECT DISTINCT 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 = 1
AND inspect_pass = true)
OR t_inspect_area.inspect_area_id IS NULL
ORDER BY t_serial_number.serial_number::int ASC

resulting error:

ERROR: for SELECT DISTINCT, ORDER BY expressions must
appear in select list

(good call onthe type of error).

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