From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: Need help combining 2 tables together
Date: Fri, 22 May 2009 07:17:46 -0700
User-Agent: KMail/1.9.10
Cc: Richard Ekblom <ens01rem@cs.umu.se>
References: <4A16AD3B.5040407@cs.umu.se>
In-Reply-To: <4A16AD3B.5040407@cs.umu.se>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200905220717.46444.aklaver@comcast.net>

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