From: James Kitambara <jameskitambara@yahoo.co.uk>
To: Richard Ekblom <ens01rem@cs.umu.se>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need help combining 2 tables together
Date: Fri, 22 May 2009 14:47:27 +0000 (GMT)
Message-ID: <200454.48323.qm@web27908.mail.ukl.yahoo.com> (raw)
Dear Richard Ekblom,
I think Mr. Adrian Klaver gave you the solution. Mine is the similar solution
SELECT message.id,topic.topic,message.message
FROM topics, messages
WHERE message.topic=topic.id order by message.id;
After executing this query you will get the following:
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
Best Regards,
Muhoji James Kitambara
Database Administrator,
B.Sc. With Computer Science and Statistics (Hons),
National Bureau of Statistics,
P.O. Box 796,
Tel : +255 22 2122722/3 Fax: +255 22 2130852,
Mobile : +255 71 3307632,
Dar es Salaam,
Tanzania.
-----------------------------------------ORGINAL MESSAGE--------------------------------
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???
>
>
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: jameskitambara@yahoo.co.uk, ens01rem@cs.umu.se
Subject: Re: Need help combining 2 tables together
In-Reply-To: <200454.48323.qm@web27908.mail.ukl.yahoo.com>
* 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