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: 20051227225419.47572.qmail@web33311.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
<Keith>

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,
       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
       )
 WHERE t_product.product_number = 1
 ORDER BY product_number,
          serial_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 
be
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 
possible
to have serial numbers without links without jobs...
then there is no 
way to
connect them to the product table without some other
data.

Kind Regards,
Keith

</keith>

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
"table":

> 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?

tia...


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


In response to

Responses

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-2014 The PostgreSQL Global Development Group