Re: SQL Count Magic Required.... Second Iteration...

Second Iteration continued...

prior text deleted due to length...

i decided to simplify the problem and only look at the
select that is supposed count the first pass pass.

the data is as follows...

t_inspect
inspect_id, sn_id
178, 200
179, 200

t_inspect_result
id, inspect_id, inspect_result_pass
27, 178, *false*
28, 179, *false*
31, 179, true

** designates first pass value, ie, not 2nd, 3rd, 4th,
5th, etc...  passes.

so, the following query should yield 0, since zero
first pass passes are logged.

it returns one row with a value of 1 (as in number
one).  if t_inspect_result_id 27 is changed to "true",
it returns two rows, both with a value of 1.

(SELECT DISTINCT ON (t_inspect_result.inspect_id)
count(inspect_result_pass) as passed
      FROM t_inspect_result, t_inspect,
t_inspect_area,
           t_serial_number,
t_link_contract_number_job_number, t_job_number,
t_product
      WHERE t_inspect.inspect_id =
t_inspect_result.inspect_id
            AND inspect_result_pass = 't'
            AND t_inspect.serial_number_id =
                t_serial_number.serial_number_id
            AND
t_serial_number.link_contract_number_job_number_id =

t_link_contract_number_job_number.link_contract_number_job_number_id
            AND
t_link_contract_number_job_number.job_number_id =
                t_job_number.job_number_id
            AND t_product.product_id =
                t_job_number.product_id
            AND t_inspect.inspect_area_id =
                t_inspect_area.inspect_area_id
            AND t_inspect.serial_number_id = '200'
            GROUP BY t_inspect_result.inspect_id,
t_inspect_result.inspect_result_timestamp

as always, any help or guidance is appreciated.

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