pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft Join
13+ messages / 10 participants
[nested] [flat]
* Left Join
@ 2000-03-05 10:43 Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
0 siblings, 1 reply; 13+ messages in thread
From: Nikolaj Lundsgaard @ 2000-03-05 10:43 UTC (permalink / raw)
To: pgsql-sql
How do make a left join in postgresql ?
/Nikolaj
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Left Join
@ 2000-03-05 14:15 Peter Eisentraut <peter_e@gmx.net>
parent: Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
0 siblings, 0 replies; 13+ messages in thread
From: Peter Eisentraut @ 2000-03-05 14:15 UTC (permalink / raw)
To: Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>; +Cc: pgsql-sql
Nikolaj Lundsgaard writes:
> How do make a left join in postgresql ?
Outer joins aren't supported yet. You can generally do something like
select a2, b2 from a, b where a1 = b1
union
select a2, NULL from a where a1 not in (select b1 from b);
which is not very extensible to more than two tables. Alternatively you
can make a function that returns the corresponding value from b2 given an
a2 value, or some default if there isn't any corresponding one.
Look into the archives for outer joins for countless specifics on this.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 13+ messages in thread
* LEFT JOIN
@ 2000-07-04 12:15 Antti Linno <alligator@all.ee>
0 siblings, 0 replies; 13+ messages in thread
From: Antti Linno @ 2000-07-04 12:15 UTC (permalink / raw)
To: pgsql-sql
Greetings.
I have a problem. I have 2 tables. E.g. work and workers. I want to
select records, that 1st table has, and the second hasn't(both have id
attribute). I mean I can't do it with is NULL, because those records don't
exist. I was shown, how it is done with mysql
select first.id,second.id_first
from first left join second on
id=id_first where id_first is NULL;
but when I tried it in psql, it said, not implemented.
Antti
^ permalink raw reply [nested|flat] 13+ messages in thread
* left join ?
@ 2000-12-23 19:16 xyzii <xyzii@kimo.com.tw>
0 siblings, 0 replies; 13+ messages in thread
From: xyzii @ 2000-12-23 19:16 UTC (permalink / raw)
To: pgsql-sql
hi all,
I try
select purc.trn_no,purcsub.item_no,purcsub.qty,emp_no,emp_na
from purc left join purcsub on (purc.trn_no=purcsub.trn_no)
left join emp on (purc.emp_no = emp.emp_no)
postgresql not run,please help,thank
^ permalink raw reply [nested|flat] 13+ messages in thread
* LEFT JOIN
@ 2001-03-20 23:00 Grant Furick <furick1@attglobal.net>
0 siblings, 0 replies; 13+ messages in thread
From: Grant Furick @ 2001-03-20 23:00 UTC (permalink / raw)
To: pgsql-sql
I am trying to output news. An article can have an image or not sometimes.
Can someone help me get this to work in Postgres?
select a.article_id, a.title, a.url, a.synopsis, a.publish_date,
c.parent_category_id, c.category_id, c.category_name,
i.server_image_name
from ((article a JOIN article_category_assoc acs ON a.article_id =
acs.article_id)
JOIN category c ON c.category_id = acs.category_id)
LEFT OUTER JOIN (image i JOIN article_image_assoc aia ON i.image_id =
aia.image_id)
ON a.article_id = aia.article_id
where i.image_type_id = 1
and a.live_date <= #CreateODBCDate(Now())#
and a.active_ind = TRUE
and a.status_id = 2
and c.category_id=#intCategoryID#
Thanks
^ permalink raw reply [nested|flat] 13+ messages in thread
* LEFT JOIN ...
@ 2001-06-18 17:26 The Hermit Hacker <scrappy@hub.org>
0 siblings, 2 replies; 13+ messages in thread
From: The Hermit Hacker @ 2001-06-18 17:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-hackers@postgresql.org
Morning ...
I'm trying to wrack my brain over something here, and no matter
how I try and look at it, I'm drawing a blank ...
I have two tables that are dependent on each other:
notes (86736 tuples) and note_links (173473 tuples)
The relationship is that one note can have several 'ppl' link'd to
it ...
I have a third table: calendar (11014 tuples) ... those calendar
entries link to a note.
So you have something like:
personA ---
personB --|--> note_links --> notes --[maybe]--> calendar entry
personC ---
now, the query I'm workign with is:
SELECT n.note, n.nid, n.type, c.act_type, c.status, nl.contact_lvl,
CASE WHEN c.act_start IS NULL
THEN date_part('epoch', n.added)
ELSE date_part('epoch', c.act_start)
END AS start
FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
ORDER BY start DESC;
Which explains out as:
NOTICE: QUERY PLAN:
Sort (cost=7446.32..7446.32 rows=1 width=88)
-> Nested Loop (cost=306.52..7446.31 rows=1 width=88)
-> Index Scan using note_links_id on note_links nl (cost=0.00..3.49 rows=1 width=16)
-> Materialize (cost=6692.63..6692.63 rows=60015 width=72)
-> Hash Join (cost=306.52..6692.63 rows=60015 width=72)
-> Seq Scan on notes n (cost=0.00..2903.98 rows=60015 width=36)
-> Hash (cost=206.22..206.22 rows=10122 width=36)
-> Seq Scan on calendar c (cost=0.00..206.22 rows=10122 width=36)
EXPLAIN
and takes forever to run ...
Now, if I eliminate the LEFT JOIN part of the above, *one* tuple is
returned ... so even with the LEFT JOIN, only *one* tuple is going to be
returned ...
Is there some way to write the above so that it evaluates:
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
returned, instead of the 86736 that are in the table?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: LEFT JOIN ...
@ 2001-06-18 18:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: The Hermit Hacker <scrappy@hub.org>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2001-06-18 18:07 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
The Hermit Hacker <scrappy@hub.org> writes:
> FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
> WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
> AND (nl.id = 15748 AND contact_lvl = 'company')
> AND n.nid = nl.nid
> ORDER BY start DESC;
> Is there some way to write the above so that it evaluates:
> first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
> returned, instead of the 86736 that are in the table?
Try adding ... AND n.nid = 15748 ... to the WHERE. It's not very
bright about making that sort of transitive-equality deduction for
itself...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: LEFT JOIN ...
@ 2001-06-18 18:18 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: The Hermit Hacker <scrappy@hub.org>
1 sibling, 0 replies; 13+ messages in thread
From: Stephan Szabo @ 2001-06-18 18:18 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
I think that using INNER JOIN between nl and n (on n.nid=nl.nid) or
joining those tables in a subquery might work.
On Mon, 18 Jun 2001, The Hermit Hacker wrote:
> Is there some way to write the above so that it evaluates:
>
> WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
> AND (nl.id = 15748 AND contact_lvl = 'company')
> AND n.nid = nl.nid
>
> first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
> returned, instead of the 86736 that are in the table?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: LEFT JOIN ...
@ 2001-06-18 18:26 The Hermit Hacker <scrappy@hub.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: The Hermit Hacker @ 2001-06-18 18:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
On Mon, 18 Jun 2001, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> > FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
> > WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
> > AND (nl.id = 15748 AND contact_lvl = 'company')
> > AND n.nid = nl.nid
> > ORDER BY start DESC;
>
> > Is there some way to write the above so that it evaluates:
> > first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
> > returned, instead of the 86736 that are in the table?
>
> Try adding ... AND n.nid = 15748 ... to the WHERE. It's not very
> bright about making that sort of transitive-equality deduction for
> itself...
n.nid is the note id ... nl.id is the contact id ...
I'm trying to pull out all notes for the company with an id of 15748:
sepick=# select * from note_links where id = 15748;
nid | id | contact_lvl | owner
-------+-------+-------------+-------
84691 | 15748 | company | f
(1 row)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: LEFT JOIN ...
@ 2001-06-18 19:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2001-06-18 19:56 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
The Hermit Hacker <scrappy@hub.org> writes:
>> Try adding ... AND n.nid = 15748 ... to the WHERE.
> n.nid is the note id ... nl.id is the contact id ...
Ooops, I misread "n.nid = nl.nid" as "n.nid = nl.id". Sorry for the
bogus advice.
Try rephrasing as
FROM (note_links nl JOIN notes n ON (n.nid = nl.nid))
LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE ...
The way you were writing it forced the LEFT JOIN to be done first,
whereas what you want is for the note_links-to-notes join to be done
first. See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: LEFT JOIN ...
@ 2001-06-18 20:17 The Hermit Hacker <scrappy@hub.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: The Hermit Hacker @ 2001-06-18 20:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
Perfect, thank you ... i knew I was overlooking something obvious ... the
query just flies now ...
On Mon, 18 Jun 2001, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> >> Try adding ... AND n.nid = 15748 ... to the WHERE.
>
> > n.nid is the note id ... nl.id is the contact id ...
>
> Ooops, I misread "n.nid = nl.nid" as "n.nid = nl.id". Sorry for the
> bogus advice.
>
> Try rephrasing as
>
> FROM (note_links nl JOIN notes n ON (n.nid = nl.nid))
> LEFT JOIN calendar c ON (n.nid = c.nid)
> WHERE ...
>
> The way you were writing it forced the LEFT JOIN to be done first,
> whereas what you want is for the note_links-to-notes join to be done
> first. See
> http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
>
> regards, tom lane
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Left join?
@ 2006-07-01 21:21 Carlos H. Reimer <carlosreimer@terra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Carlos H. Reimer @ 2006-07-01 21:21 UTC (permalink / raw)
To: pgsql-sql
Hi,
In the following table, codsol, codate and codfec are foreign keys
referencing table func and I need some help to codify a SELECT command that
produces the following result set but instead of codsol, codate and codfec I
need the respectives names (column nome from table func).
postgres=# select * from reqtran;
codreq | codsol | codate | codfec
--------+--------+--------+--------
1 | | |
2 | 1 | |
3 | 1 | 1 |
4 | 1 | 1 | 1
(4 rows)
Thanks in advance,
Carlos
__________________________________________________
Table definitions:
postgres=# \d func
Table "public.func"
Column | Type | Modifiers
--------+---------+-----------
codfun | integer | not null
nome | text |
Indexes:
"func_pkey" PRIMARY KEY, btree (codfun)
postgres=# \d reqtran
Table "public.reqtran"
Column | Type | Modifiers
--------+---------+-----------
codreq | integer | not null
codsol | integer |
codate | integer |
codfec | integer |
Indexes:
"reqtran_pkey" PRIMARY KEY, btree (codreq)
Foreign-key constraints:
"reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)
"reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun)
"reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)
__________________________________________________
Table contents:
postgres=# select * from func;
codfun | nome
--------+-------
1 | nome1
2 | nome2
3 | nome3
(3 rows)
postgres=# select * from reqtran;
codreq | codsol | codate | codfec
--------+--------+--------+--------
1 | | |
2 | 1 | |
3 | 1 | 1 |
4 | 1 | 1 | 1
(4 rows)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Left join?
@ 2006-07-01 21:44 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Carlos H. Reimer <carlosreimer@terra.com.br>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Broersma Jr @ 2006-07-01 21:44 UTC (permalink / raw)
To: carlosreimer@terra.com.br; pgsql-sql
> In the following table, codsol, codate and codfec are foreign keys
> referencing table func and I need some help to codify a SELECT command that
> produces the following result set but instead of codsol, codate and codfec I
> need the respectives names (column nome from table func).
>
> postgres=# select * from reqtran;
> codreq | codsol | codate | codfec
> --------+--------+--------+--------
> 1 | | |
> 2 | 1 | |
> 3 | 1 | 1 |
> 4 | 1 | 1 | 1
> postgres=# \d func
> Table "public.func"
> Column | Type | Modifiers
> --------+---------+-----------
> codfun | integer | not null
> nome | text |
> Indexes:
> "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun)
> "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun)
> "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun)
Would this do what you need?
select R1.codreq,
CS.nome,
CD.nome,
CF.nome
from rectran as R1
left join func as CS on (R1.codsol=CS.codefun)
left join func as CD on (R1.codate=CD.codefun)
left join func as CF on (R1.codfec=CF.codefun)
;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-07-01 21:44 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-05 10:43 Left Join Nikolaj Lundsgaard <Nikolaj@kampsax.dtu.dk>
2000-03-05 14:15 ` Peter Eisentraut <peter_e@gmx.net>
2000-07-04 12:15 LEFT JOIN Antti Linno <alligator@all.ee>
2000-12-23 19:16 left join ? xyzii <xyzii@kimo.com.tw>
2001-03-20 23:00 LEFT JOIN Grant Furick <furick1@attglobal.net>
2001-06-18 17:26 LEFT JOIN ... The Hermit Hacker <scrappy@hub.org>
2001-06-18 18:07 ` Re: LEFT JOIN ... Tom Lane <tgl@sss.pgh.pa.us>
2001-06-18 18:26 ` Re: LEFT JOIN ... The Hermit Hacker <scrappy@hub.org>
2001-06-18 19:56 ` Re: LEFT JOIN ... Tom Lane <tgl@sss.pgh.pa.us>
2001-06-18 20:17 ` Re: LEFT JOIN ... The Hermit Hacker <scrappy@hub.org>
2001-06-18 18:18 ` Re: LEFT JOIN ... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2006-07-01 21:21 Left join? Carlos H. Reimer <carlosreimer@terra.com.br>
2006-07-01 21:44 ` Re: Left join? Richard Broersma Jr <rabroersma@yahoo.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