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@comcast.net