DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=VZ7udHYs+CcURMffYrQq8lKBAJBC/FujfAeYUeB9xX9w6EFUupqJAoU3huA2agdNJnztEFikwJ5THYkj7nz6U3wcj6lLAZZpoEjNdV0r3ziIg47nX64X9g+TraayGZVJaqSPLcsYUw3GqV5MZE5BJ2vcx2Qg7esZQTdd0zb8wI4=
	;
Message-ID: <20060602200640.91950.qmail@web33304.mail.mud.yahoo.com>
Date: Fri, 2 Jun 2006 13:06:40 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Using Query Result in WHERE Clause
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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