Re: Problem with joining two tables
От | Thomas Burdairon |
---|---|
Тема | Re: Problem with joining two tables |
Дата | |
Msg-id | 8264922A-6054-41BC-9506-216A6D188288@entelience.com обсуждение исходный текст |
Ответ на | Problem with joining two tables (Przemyslaw Bojczuk <pb2@gis.umcs.lublin.pl>) |
Ответы |
Re: Problem with joining two tables
(Przemyslaw Bojczuk <pb2@gis.umcs.lublin.pl>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: