if you want topics listed which don't yet have messages try select t.id, t.topic, m.id, m.message from topics t left join messages m on m.topic = t.id;
On Fri, May 22, 2009 at 8:47 AM, James Kitambara <jameskitamb...@yahoo.co.uk > wrote: > 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??? > > > > > > >
