pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedjoins INNER, OUTER, LEFT, RIGHT, FULL, ...
4+ messages / 3 participants
[nested] [flat]
* joins INNER, OUTER, LEFT, RIGHT, FULL, ...
@ 2004-11-23 17:03 Roderick A. Anderson <raa@mailporter.net>
2004-11-24 05:06 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Jaime Casanova <systemguards@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Roderick A. Anderson @ 2004-11-23 17:03 UTC (permalink / raw)
To: pgsql-admin
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]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ...
2004-11-23 17:03 joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
@ 2004-11-24 05:06 ` Jaime Casanova <systemguards@yahoo.com>
2004-11-24 05:32 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
0 siblings, 1 reply; 4+ messages in thread
From: Jaime Casanova @ 2004-11-24 05:06 UTC (permalink / raw)
To: pgsql-admin
--- "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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ...
2004-11-23 17:03 joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
2004-11-24 05:06 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Jaime Casanova <systemguards@yahoo.com>
@ 2004-11-24 05:32 ` Roderick A. Anderson <raa@mailporter.net>
2004-11-24 06:32 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Christian Fowler <spider@viovio.com>
0 siblings, 1 reply; 4+ messages in thread
From: Roderick A. Anderson @ 2004-11-24 05:32 UTC (permalink / raw)
To: Jaime Casanova <systemguards@yahoo.com>; +Cc: pgsql-admin
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]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ...
2004-11-23 17:03 joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
2004-11-24 05:06 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Jaime Casanova <systemguards@yahoo.com>
2004-11-24 05:32 ` Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
@ 2004-11-24 06:32 ` Christian Fowler <spider@viovio.com>
0 siblings, 0 replies; 4+ messages in thread
From: Christian Fowler @ 2004-11-24 06:32 UTC (permalink / raw)
To: Roderick A. Anderson <raa@mailporter.net>; +Cc: Jaime Casanova <systemguards@yahoo.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-11-24 06:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-23 17:03 joins INNER, OUTER, LEFT, RIGHT, FULL, ... Roderick A. Anderson <raa@mailporter.net>
2004-11-24 05:06 ` Jaime Casanova <systemguards@yahoo.com>
2004-11-24 05:32 ` Roderick A. Anderson <raa@mailporter.net>
2004-11-24 06:32 ` Christian Fowler <spider@viovio.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox