Re: Need help combining 2 tables together

----- Original Message -----

From: James Kitambara

To: Richard Ekblom

Cc: pgsql-sql@postgresql.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??? > >