Обсуждение: 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/