Обсуждение: joins INNER, OUTER, LEFT, RIGHT, FULL, ...

Be gentle as I come from an Oracle background a long time ago.

I am trying to join several tables with a couple of tables having a one
to many relationship.  I got one query working pretty well but the issue
comes about when I add one more table with a one-to-one relationship.  I
get a semi/pseudo Cartesian join.

I'm not asking for the exact solution but a pointer to a _good_
reference on complex joins.  My searching on the PostgreSQL site and
Google only turn up examples of simple joins.  I did forget to look at
the two PostgreSQL books I have at home -- Bruce's and Practical
PostgreSQL -- last night so if there are some in them a light tap with
the clue-stick will suffice.


Thanks,
Rod
--



---
[This E-mail scanned for viruses by Declude Virus]

 --- "Roderick A. Anderson" <raa@mailporter.net>
escribió:
> Be gentle as I come from an Oracle background a long
> time ago.
>
> I am trying to join several tables with a couple of
> tables having a one
> to many relationship.  I got one query working
> pretty well but the issue
> comes about when I add one more table with a
> one-to-one relationship.  I
> get a semi/pseudo Cartesian join.

> I'm not asking for the exact solution but a pointer
> to a _good_
> reference on complex joins.  My searching on the
> PostgreSQL site and
> Google only turn up examples of simple joins.  I did
> forget to look at
> the two PostgreSQL books I have at home -- Bruce's
> and Practical
> PostgreSQL -- last night so if there are some in
> them a light tap with
> the clue-stick will suffice.
>
>

http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-JOIN

What do you mean with *complex joins*?

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova wrote:

>http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-JOIN
>
>
Been there, done that.  Bought several tee-shirts.

>What do you mean with *complex joins*?
>
>

SELECT first, last, username || '@' || dom.domain as emailaddress
  FROM cust_main cm
   LEFT OUTER JOIN cust_pkg    cp ON ( cm.custnum = cp.custnum )
   LEFT OUTER JOIN cust_svc    cs ON ( cp.pkgnum  = cs.pkgnum )
   LEFT OUTER JOIN svc_email   em ON ( cs.svcnum  = em.svcnum )
   LEFT OUTER JOIN svc_domain dom ON ( em.domain  = dom.svcnum )
  WHERE zip = '99999'
    AND username || '@' || dom.domain != '@'

Which worked until I added one more table with a one-to-one relation to
cust_main.custnum.  Then I got several tuples (2+) for each row above.


Rod
--

---
[This E-mail scanned for viruses by Declude Virus]

On Tue, 23 Nov 2004, Roderick A. Anderson wrote:

> Jaime Casanova wrote:
>
>> http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-JOIN
>>
> Been there, done that.  Bought several tee-shirts.
>
>> What do you mean with *complex joins*?
>>
>
> SELECT first, last, username || '@' || dom.domain as emailaddress
> FROM cust_main cm
>  LEFT OUTER JOIN cust_pkg    cp ON ( cm.custnum = cp.custnum )
>  LEFT OUTER JOIN cust_svc    cs ON ( cp.pkgnum  = cs.pkgnum )
>  LEFT OUTER JOIN svc_email   em ON ( cs.svcnum  = em.svcnum )
>  LEFT OUTER JOIN svc_domain dom ON ( em.domain  = dom.svcnum )
> WHERE zip = '99999'
>   AND username || '@' || dom.domain != '@'
>
> Which worked until I added one more table with a one-to-one relation to
> cust_main.custnum.  Then I got several tuples (2+) for each row above.

try:

SELECT first, last, username || '@' || dom.domain as emailaddress
FROM cust_main cm
  INNER JOIN one_more om ON ( om.custnum = cm.custnum )
  LEFT OUTER JOIN cust_pkg    cp ON ( cm.custnum = cp.custnum )
  LEFT OUTER JOIN cust_svc    cs ON ( cp.pkgnum  = cs.pkgnum )
  LEFT OUTER JOIN svc_email   em ON ( cs.svcnum  = em.svcnum )
  LEFT OUTER JOIN svc_domain dom ON ( em.domain  = dom.svcnum )
WHERE zip = '99999'
   AND username || '@' || dom.domain != '@'

or

SELECT first, last, username || '@' || dom.domain as emailaddress
FROM one_more om, cust_main cm
  LEFT OUTER JOIN cust_pkg    cp ON ( cm.custnum = cp.custnum )
  LEFT OUTER JOIN cust_svc    cs ON ( cp.pkgnum  = cs.pkgnum )
  LEFT OUTER JOIN svc_email   em ON ( cs.svcnum  = em.svcnum )
  LEFT OUTER JOIN svc_domain dom ON ( em.domain  = dom.svcnum )
WHERE zip = '99999'
   AND om.custnum=cm.custnum
   AND username || '@' || dom.domain != '@'


The explicit join syntax was freaky for me too at first,  but after
several years, I prefer it now, since you can easily control your left
outer joins


[ \ /
[ >X<   Christian Fowler      | spider AT viovio.com
[ / \   http://www.viovio.com | http://www.tikipro.org