Re: Combine, Merge, Concatenate

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|text3
> 002|text6|text1|text2
>
> 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/interactive/tutorial-join.html
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.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