Skip site navigation (1) Skip section navigation (2)

Query problem

From: <operationsengineer1(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Query problem
Date: 2006-01-16 19:26:25
Message-ID: 20060116192625.22949.qmail@web33315.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
while working with and testing my new favorite query,
i ran into some behavior that i didn't expect.

the query is as follows:

SELECT DISTINCT t_product.product_id,
       t_product.product_number,
       t_serial_number.serial_number_id,
       t_serial_number.serial_number::int
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)
ORDER BY t_serial_number.serial_number::int ASC

my purpose is to find the serial number that doesn't
have at least one TRUE inspect_pass value by serial
number / inspect area.

starting with no data:  i enter in any number of
production inspect (t_inspect_area.inspect_area_id =
1) fails for serial number 1 (s/n 1).  when i query
inspect_id = 1, s/n 1 returned as expected.  i enter a
true value for t_inspect_area.inspect_area_id = 1 and
s/n 1 no longer returns when i query inspect_area_id =
1 - just as expected.

the problem begins when i move to qa inspection
(t_inspect_area.inspect_area_id = 2) and enter a true
value for s/n 1.

not only does a query for inspect_id = 2 (qa
inspect_area) return s/n 1, now a query based on
inspect_id returns s/n 1.

i'm stumped as to why.  any help would be appreciated.

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

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2006-01-16 19:46:39
Subject: Re: Query problem - Possible solution
Previous:From: Andreas KretschmerDate: 2006-01-16 16:46:08
Subject: Re: Help with query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group