Re: Complex Query Help- For Me, Anyway - Mailing list pgsql-novice

operationsengineer1@yahoo.com wrote:
> <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...

You are right about the output.  Serial number 3 would not be included
if it is paired only with part number 2.

Again you are correct.  A LEFT JOIN implies an OUTER JOIN.  From the
manual "The words INNER and OUTER are optional in all forms. INNER is
the default; LEFT, RIGHT, and FULL imply an outer join."

Yes.  An INNER JOIN only returns records that match the join condition.
  When you add the left and right you either get the left table with
nulls on the right or the right table with nulls on the left respectively.

The manual here
http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html
explains it better than I can.

So in your situation if you use the LEFT JOIN you could end up with
serial numbers that did not have a part number.  Obviously they would
not be selectable by part number unless you matched on part number IS
NULL.  In a real world model that doesn't make sense so it would
probably suit your needs better to use just plain joins or for
readability explicitly state INNER JOIN.  Of course you have to be the
final judge on that. ;-)

If it were me I would make sure that there were some foreign key
assignments preventing a serial_number without a link_id and a link_id
without a job_number_id etc. etc.

HTH

--
Kind Regards,
Keith