PostgreSQL Discussion - Combine, Merge, Concatenate

Author

Combine, Merge, Concatenate

Andrej Kastrin

2006-01-23, 11:24 am

Michael Fuhr

2006-01-27, 3:23 am

On Mon, Jan 23, 2006 at 11:06:22AM +0000, Andrej Kastrin wrote:
> I have a problem, which is quite hard to solve it in Perl (for me, of
> course). I have to tables, which looks like
>
> First Table:
>
> 1|001|002|003
> 2|006|04|002
>
> Second Table:
>
> 001|text1|text2|text
3
> 002|text6|text1|text
2
>
> Now I would like to concatenate this two tables into new table:
>
> Third Table:
>
> 1|text1|text2|text3|
text6 *
> 2|etc

I think you're looking for a query known as a "join." Use a search
engine with words like "sql join tutorial" or check out the relevant
parts of the PostgreSQL documentation:

http://www.postgresql.org/docs/8.1/...orial-join.html
http://www.postgresql.org/docs/8.1/...xpressions.html

Here's an example that might be similar to what you're looking for:

CREATE TABLE foo (
id integer PRIMARY KEY,
col1 text NOT NULL,
col2 text NOT NULL
);

CREATE TABLE bar (
id integer PRIMARY KEY,
fooid1 integer NOT NULL REFERENCES foo,
fooid2 integer NOT NULL REFERENCES foo
);

INSERT INTO foo VALUES (1, 'text1', 'text2');
INSERT INTO foo VALUES (2, 'text3', 'text4');
INSERT INTO foo VALUES (3, 'text5', 'text6');
INSERT INTO foo VALUES (4, 'text7', 'text8');

INSERT INTO bar VALUES (1, 1, 2);
INSERT INTO bar VALUES (2, 3, 4);

SELECT b.id,
f1.col1, f1.col2,
f2.col1 AS col3, f2.col2 AS col4
FROM bar AS b
JOIN foo AS f1 ON f1.id = b.fooid1
JOIN foo AS f2 ON f2.id = b.fooid2;

id | col1 | col2 | col3 | col4
----+-------+-------+-------+-------
1 | text1 | text2 | text3 | text4
2 | text5 | text6 | text7 | text8
(2 rows)

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links