Re: Need help combining 2 tables together

On Friday 22 May 2009 6:48:43 am Richard Ekblom wrote:
> 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

test=# SELECT m.id,t.topic,m.message from topics as t,messages as m where
m.topic=t.id order by m.id;
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

--
Adrian Klaver
aklaver(at)comcast(dot)net