From: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
---|---|
To: | Richard Ekblom <ens01rem(at)cs(dot)umu(dot)se> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Need help combining 2 tables together |
Date: | 2009-05-22 14:47:27 |
Message-ID: | 200454.48323.qm@web27908.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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???
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2009-05-22 14:55:09 | Allow column type to change without worrying about view dependencies |
Previous Message | Adrian Klaver | 2009-05-22 14:17:46 | Re: Need help combining 2 tables together |