Thread: 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...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On Thu, Jan 12, 2006 at 09:08:59AM -0800, operationsengineer1@yahoo.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. > 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
> 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
> On Thu, Jan 12, 2006 at 09:08:59AM -0800,
> operationsengineer1@yahoo.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
On Thu, Jan 12, 2006 at 12:07:12PM -0800, operationsengineer1@yahoo.com wrote:
> SELECT DISTINCT t_product.product_id,
> t_product.product_number,
> t_serial_number.serial_number_id,
> t_serial_number.serial_number
[...]
> ORDER BY t_serial_number.serial_number::int ASC
>
> resulting error:
>
> ERROR: for SELECT DISTINCT, ORDER BY expressions must
> appear in select list
What data type is serial_number? I'm guessing not integer, else
you wouldn't be casting it to integer in the ORDER BY clause. If
you need that cast then also use it in the SELECT list:
SELECT DISTINCT t_product.product_id,
t_product.product_number,
t_serial_number.serial_number_id,
t_serial_number.serial_number::int
--
Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Jan 12, 2006 at 12:07:12PM -0800, > operationsengineer1@yahoo.com wrote: > > SELECT DISTINCT t_product.product_id, > > t_product.product_number, > > t_serial_number.serial_number_id, > > t_serial_number.serial_number > [...] > > ORDER BY t_serial_number.serial_number::int ASC > > > > resulting error: > > > > ERROR: for SELECT DISTINCT, ORDER BY expressions > must > > appear in select list > > What data type is serial_number? I'm guessing not > integer, else > you wouldn't be casting it to integer in the ORDER > BY clause. If > you need that cast then also use it in the SELECT > list: > > SELECT DISTINCT t_product.product_id, > t_product.product_number, > t_serial_number.serial_number_id, > t_serial_number.serial_number::int > > -- > Michael Fuhr > Excellent! it worked - thank you very much, Michael. i would've never made that connection. serial_number is text. i went that route b/c i thought i'd never do any math on serial_number. then i learned how text sorts on numeric data -lol- as an aside, the forms class i use was only outputting one instance out of three in the array i created. three in, one out. this process enabled the highlighting of unexpected behavior in another piece of software. thanks again. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com