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

Re: Complex Query Help- For Me, Anyway

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: <operationsengineer1(at)yahoo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Complex Query Help- For Me, Anyway
Date: 2005-12-27 19:11:51
Message-ID: 20051227190040.M35222@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 27 Dec 2005 10:29:44 -0800 (PST), operationsengineer1 wrote
> i'm having a problem sorting out a query - the biggest
> problem is that i'm not wrapping my mind around the
> thought process required to solve the issue (hint -
> step by step thought process guidance is what is
> really important to me, not so much the answer - i
> want to be able to address this situation next time,
> too).
> 
> the table structure (shortened for brevity):
> 
> t_sn
> link_id
> serial_number
> 
> t_link
> link_id
> job_number_id
> contract_id
> 
> t_job_number
> job_number_id
> product_id
> 
> t_product
> product_id
> product_number
> 
> the links:
> 
> t_sn.link_id <-> t_link.link_id
> t_link.job_number_id <-> t_job_number.job_number_id
> t_job_number.product_id <-> t_product.product_id
> 
> i'm trying to create an array a multidimensional array
> of all serial numbers by product number.  for example:
> 
> prod1
> sn1
> sn2
> sn3
> 
> prod2
> sn4
> sn5
> sn6
> 
> this allows my user to choose a product and then view
> a select box populated with only the associated serial
> numbers.
> 
> i *think* i'm trying to find product_id,
> product_number, sn_id, serial_number where the
> t_sn.link_id = t_link.link_id AND t_link.job_number_id
> = t_job_number.job_number_id AND
> t_job_number.product_id = t_product.product_id.
> 
> i receive the following error:
> 
> ERROR:  missing FROM-clause entry for table "t_link"
> 
> the last time i saw this error, a few people pointed
> me to the solution of using a subquery.
> 
> excluding this error, i'm thinking i'm wrong b/c i
> don't see how the above "logic" can produce multiple
> serial number results.
> 
> i'm not grasping what my thought process should be
> when i view this situation.
> 
> any help is greatly appreciated.

I don't have the experience to help you with the array portion of your question
but perhaps this query will help.

SELECT t_product.product_number,
       t_sn.serial_number
  FROM t_sn
  LEFT JOIN t_link
    ON ( t_sn.link_id = t_link.link_id )
  LEFT JOIN t_job_number
    ON ( t_link.job_number_id = t_job_number.job_number_id )
  LEFT JOIN t_product
    ON ( t_job_number.product_id = t_product.product_id )
 ORDER BY product_number,
          serial_number;

Add a WHERE BY clause if you only want data for one product_number.

HTH

Kind Regards,
Keith

In response to

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-12-27 21:46:27
Subject: Re: Complex Query Help- For Me, Anyway
Previous:From: operationsengineer1Date: 2005-12-27 18:29:44
Subject: Complex Query Help- For Me, Anyway

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