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

Re: Complex Query Help- For Me, Anyway

From: <operationsengineer1(at)yahoo(dot)com>
To: KeithW(at)narrowpathinc(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 22:54:19
Message-ID: (view raw or whole thread)
Lists: pgsql-novice

Oops. that WHERE BY happened when my fingers got ahead
of my brain.  It 
is simply a WHERE clause.  Given the following
corrected query.

SELECT t_product.product_number,
  FROM t_sn
  LEFT JOIN t_link
    ON ( t_sn.link_id =
  LEFT JOIN t_job_number
    ON ( t_link.job_number_id =
  LEFT JOIN t_product
    ON ( t_job_number.product_id =
 WHERE t_product.product_number = 1
 ORDER BY product_number,

I would expect a result similar to this.

product_number | serial_number
1              | 1
1              | 2
1              | 3

The data that is used to connect the tables is not
returned because it 
was not
requested.  If there is a serial_number without a
link_id it will not 
returned.  Similarly if there is a link_id without a
job_number_id it 
and its
serial_numbers if any will not be in the result.  The
use of foreign 
keys in
your tables could prevent that IF it is appropriate. 
If indeed it is 
to have serial numbers without links without jobs...
then there is no 
way to
connect them to the product table without some other

Kind Regards,


Keith, i want to make sure we are on the same page.  i
think the output from your query should be as follows:

product_number | serial_number
1              | 1
1              | 2

serial number 3 is linked to product 2 in the original

> sn, lk, jn, pn (ids)
> 1,  1,  1,  1
> 2,  2,  2,  1
> 3,  3,  2,  2 (<--- notice "2" here)
> etc...

also, i thought a left join included everything on the
left - even if there was no match on the right.  i
guess this is known as a "left outer join" (i just did
some research). does an inner join only return values
with matches?

does postgresql's left join refer to a left inner join
by default?


Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 

In response to


pgsql-novice by date

Next:From: Keith WorthingtonDate: 2005-12-28 01:22:26
Subject: Re: Complex Query Help- For Me, Anyway
Previous:From: Keith WorthingtonDate: 2005-12-27 22:18:40
Subject: Re: Complex Query Help- For Me, Anyway

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