Re: Complex Query Help- For Me, Anyway

<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