From: | "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> |
---|---|
To: | "James Kitambara" <jameskitambara(at)yahoo(dot)co(dot)uk>, "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 15:31:38 |
Message-ID: | 01c601c9daf2$65dc0a70$ec5a3d0a@marktestcr.marktest.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I guess this is pretty much the same
as doing
SELECT message.id,topic.topic,message.message
FROM topics
JOIN messages
ON topics.id = message.topic
ORDER BY message.ID
Ain't I right?
Best,
Oliveiros
----- Original Message -----
From: James Kitambara
To: Richard Ekblom
Cc: pgsql-sql(at)postgresql(dot)org
Sent: Friday, May 22, 2009 3:47 PM
Subject: Re: [SQL] Need help combining 2 tables together
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 | Rob Sargent | 2009-05-22 15:39:09 | Re: Need help combining 2 tables together |
Previous Message | Emi Lu | 2009-05-22 14:55:09 | Allow column type to change without worrying about view dependencies |