pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing Query Result in WHERE Clause
2+ messages / 2 participants
[nested] [flat]
* Using Query Result in WHERE Clause
@ 2006-06-02 20:06 operationsengineer1@yahoo.com
2006-06-05 11:10 ` Re: Using Query Result in WHERE Clause Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 2+ messages in thread
From: operationsengineer1@yahoo.com @ 2006-06-02 20:06 UTC (permalink / raw)
To: pgsql-sql
hi all,
SUMMARY:
i want to replace
AND t_inspect.inspect_pass = 'f'
with a complex query that yields the same result. the
complex query is in the form of...
SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass,
t_inspect_result.inspect_result_id
FROM t_inspect_result, t_inspect, t_serial_number,
t_product, t_job_number,
t_link_contract_number_job_number,
t_inspect_area
WHERE -- lots of noise here
ORDER BY t_inspect.inspect_id,
inspect_result_timestamp DESC
can i do this? if so, how?
DETAILED EXAMPLE:
i have the following query (please focus on the *key*
element, the rest of the query is just noise):
SELECT t_product.product_id, t_product.product_number,
t_serial_number.serial_number_id,
t_serial_number.serial_number,
t_inspect_result.inspect_result_id,
t_inspect_area.inspect_area || ', ' ||
t_inspect_result.inspect_result_timestamp::time
FROM t_product, t_job_number,
t_link_contract_number_job_number,
t_serial_number, t_inspect, t_inspect_area,
t_inspect_result
WHERE t_product.product_id = t_job_number.product_id
AND t_job_number.job_number_id =
t_link_contract_number_job_number.job_number_id
AND
t_link_contract_number_job_number.link_contract_number_job_number_id
=
t_serial_number.link_contract_number_job_number_id
AND t_serial_number.serial_number_id =
t_inspect.serial_number_id
AND t_inspect_area.inspect_area_id =
t_inspect.inspect_area_id
AND t_inspect.inspect_id = t_inspect_result.inspect_id
-- the statement below is *key*
AND t_inspect.inspect_pass = 'f'
-- the statement above is *key*
ORDER BY t_product.product_number ASC,
serial_number::integer ASC,
t_inspect_result.inspect_result_timestamp DESC
the purpose of the query is to a multi-dimensional
array to populate a series of linked select boxes.
only those sets of values that haven't already passed
an inspection are listed, hence the
AND t_inspect.inspect_pass = 'f'
line.
in order to be able to include...
AND t_inspect.inspect_pass = 'f'
...i had to repeat data (added inspect_pass column in
t_inspect) when that data already exists (the latest
date boolean value in
t_inspect_result.inspect_result_pass).
t_inspect.inspect pass records the current state of a
unit in inspection (pass or fail), while
t_inspect_result.inspect_result_pass records all
inspect results until a pass is achieved (eg, f, f, f,
f, f, f, f, f, t).
anyway, i want to incorporate a query that finds this
last date inspect_pass_result value and replace...
AND t_inspect.inspect_pass = 'f'
the query that pulls all the latest date values is...
SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass,
t_inspect_result.inspect_result_id
FROM t_inspect_result, t_inspect, t_serial_number,
t_product, t_job_number,
t_link_contract_number_job_number,
t_inspect_area
WHERE t_inspect_result.inspect_id =
t_inspect.inspect_id
AND t_inspect.serial_number_id =
t_serial_number.serial_number_id
AND t_product.product_id = t_job_number.product_id
AND t_job_number.job_number_id =
t_link_contract_number_job_number.job_number_id
AND
t_link_contract_number_job_number.link_contract_number_job_number_id
=
t_serial_number.link_contract_number_job_number_id
AND t_serial_number.serial_number_id =
t_inspect.serial_number_id
AND t_inspect_area.inspect_area_id =
t_inspect.inspect_area_id
AND t_inspect.inspect_id =
t_inspect_result.inspect_id
ORDER BY t_inspect.inspect_id,
inspect_result_timestamp DESC
tia...
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using Query Result in WHERE Clause
2006-06-02 20:06 Using Query Result in WHERE Clause operationsengineer1@yahoo.com
@ 2006-06-05 11:10 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Andrew Sullivan @ 2006-06-05 11:10 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jun 02, 2006 at 01:06:40PM -0700, operationsengineer1@yahoo.com wrote:
> hi all,
>
> SUMMARY:
>
> i want to replace
>
> AND t_inspect.inspect_pass = 'f'
>
> with a complex query that yields the same result. the
> complex query is in the form of...
[snip]
But your complex query isn't in the form where anything will come out
"= 'f'". You have too many columns in the SELECT.
What you prabably need to do is rewrite it as a subquery in the FROM
clause, alias it as (say) alias_t_inspect, and then use your
condition to get you what you want. I won't warrant what the
performance will be, however.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-06-05 11:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-02 20:06 Using Query Result in WHERE Clause operationsengineer1@yahoo.com
2006-06-05 11:10 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox