Search Postgresql Archives

Problem with joining two tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux