Обсуждение: Problem with joining two tables
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/
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
am Wed, dem 05.12.2007, um 14:42:32 +0100 mailte Przemyslaw Bojczuk folgendes: > 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 > Okay, let's try: first i create your tables like above: test=*# select * from t1; id | stuff ----+------- 1 | sth1 2 | sth2 3 | sth3 4 | sth4 5 | sth5 (5 rows) test=*# select * from t2; id | des | etc ----+-------+------ 1 | desc1 | etc1 2 | desc2 | etc2 2 | desc3 | etc3 2 | desc4 | etc4 3 | desc5 | etc5 | desc6 | etc6 5 | desc7 | etc7 (7 rows) And now: test=*# select t1.id, t1.stuff, t2.des, t2.etc from t1, t2 where t1.id=t2.id; id | stuff | des | 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 (6 rows) is this your expected result? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Thomas Burdairon wrote: > hope this help. Thank you, it helped a lot! It was a part of a bigger problem (involving PostGIS, Mapserver et al.) and I *thought* I traced it down to this join, but now it's clear the problem lies completely elsewhere. Thanks again! PB -- Geographical Information Systems Laboratory Institute of Earth Sciences, UMCS http://gis.umcs.lublin.pl/en/