From: Richard Ekblom <ens01rem@cs.umu.se>
To: pgsql-sql@postgresql.org
Subject: Need help combining 2 tables together
Date: Fri, 22 May 2009 15:48:43 +0200
Message-ID: <4A16AD3B.5040407@cs.umu.se> (raw)
Hello
I have frequently encountered the need of combining two tables into one.
First, please take a look at the following table setups...
CREATE TABLE topics (
id SERIAL PRIMARY KEY,
topic TEXT NOT NULL
);
CREATE TABLE messages (
id SERIAL PRIMARY KEY,
topic INTEGER REFERENCES topics(id),
message TEXT NOT NULL
);
Example of a topics table:
ID TOPIC
1 Are squares better then circles?
2 My favorite food
Example of a messages table:
ID TOPIC MESSAGE
1 2 I like lasagna!
2 2 Pizza is also a favorite
3 1 I like circles, they remind me of pizza
Notice that the number of topics may differ from the number of messages.
Now I want to combine these tables with a single SELECT to get...
Combined table:
ID TOPIC MESSAGE
1 My favorite food I like lasagna!
2 My favorite food Pizza is also a favorite
3 Are squares better then circles? I like circles, they remind me
of pizza
I have seen different examples of this with something called JOIN but
they always give me only two rows. How can I do this when the two tables
may have different sizes to produce exactly the combined table above???
Some SQL for Postgres if you want to set up this example...
CREATE TABLE topics (id SERIAL PRIMARY KEY,topic TEXT NOT NULL);
CREATE TABLE messages (id SERIAL PRIMARY KEY,topic INTEGER REFERENCES
topics(id),message TEXT NOT NULL);
INSERT INTO topics(topic) VALUES('Are squares better then circles?');
INSERT INTO topics(topic) VALUES('My favorite food');
INSERT INTO messages(topic,message) VALUES(2, 'I like lasagna!');
INSERT INTO messages(topic,message) VALUES(2, 'Pizza is also a favorite');
INSERT INTO messages(topic,message) VALUES(1, 'I like circles, they
remind me of pizza');
SELECT * FROM topics;
SELECT * FROM messages;
Thanks in advance
/RE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ens01rem@cs.umu.se
Subject: Re: Need help combining 2 tables together
In-Reply-To: <4A16AD3B.5040407@cs.umu.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox