On 5 déc. 07, at 14:42, Przemyslaw Bojczuk wrote:
Hello!
I have a problem joining two tables. I tried various types of join and
none seems to work as I expect
Table 1:
id | stuff
-----------
1 | sth1
2 | sth2
3 | sth3
4 | sth4
5 | sth5
.. | ...
Table 2:
id | desc | etc
------------------
1 | desc1 | etc1
2 | desc2 | etc2
2 | desc3 | etc3
2 | desc4 | etc4
3 | desc5 | etc5
| desc6 | etc6
5 | desc7 | etc7
.. | ... | ...
I need something like:
id | stuff | desc | etc
-------------------------
1 | sth1 | desc1 | etc1
2 | sth2 | desc2 | etc2
2 | sth2 | desc3 | etc3
2 | sth2 | desc4 | etc4
3 | sth3 | desc5 | etc5
5 | sth5 | desc7 | etc7
So: join by id, discard rows that don't match any row from the other
table, add separate row for each row from table 2 that matches the
same
row from table 1.
So far the best I could get (using inner join) was something like:
id | stuff | desc | etc
-------------------------
1 | sth1 | desc1 | etc1
2 | sth2 | desc2 | etc2
2 | sth2 | desc2 | etc2
2 | sth2 | desc2 | etc2
3 | sth3 | desc5 | etc5
5 | sth5 | desc7 | etc7
(i.e. multiplied one row from table 2 instead of separate rows
matching
the same row from table 1)
right/left/full (outer) also seem to do the same thing (multiply one
row) and I don't know any other join methods.
Is there a way to accomplish what I am trying to do? Or maybe I am
missing something?
Thanks in advance!
PB
--
Geographical Information Systems Laboratory
Institute of Earth Sciences, UMCS
http://gis.umcs.lublin.pl/en/
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
CREATE TABLE test1(id int, stuff text);
CREATE TABLE test2(id int, descr text, etc text);
INSERT INTO test1(id, stuff) VALUES (1, 'sth1');
INSERT INTO test1(id, stuff) VALUES (2, 'sth2');
INSERT INTO test1(id, stuff) VALUES (3, 'sth3');
INSERT INTO test1(id, stuff) VALUES (4, 'sth4');
INSERT INTO test1(id, stuff) VALUES (5, 'sth5');
INSERT INTO test2(id, descr, etc) VALUES (1, 'desc1', 'etc1');
INSERT INTO test2(id, descr, etc) VALUES (2, 'desc2', 'etc2');
INSERT INTO test2(id, descr, etc) VALUES (2, 'desc3', 'etc3');
INSERT INTO test2(id, descr, etc) VALUES (2, 'desc4', 'etc4');
INSERT INTO test2(id, descr, etc) VALUES (3, 'desc5', 'etc5');
INSERT INTO test2(id, descr, etc) VALUES (null, 'desc6', 'etc6');
INSERT INTO test2(id, descr, etc) VALUES (5, 'desc7', 'etc7');
SELECT t1.id, t1.stuff, t2.descr, t2.etc
FROM test1 t1
INNER JOIN test2 t2 ON t1.id = t2.id;
id | stuff | descr | etc
----+-------+-------+------
1 | sth1 | desc1 | etc1
2 | sth2 | desc2 | etc2
2 | sth2 | desc3 | etc3
2 | sth2 | desc4 | etc4
3 | sth3 | desc5 | etc5
5 | sth5 | desc7 | etc7
seems OK for me
hope this help.
Tom
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
