Re: Need help combining 2 tables together

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 <jameskitambara(at)yahoo(dot)co(dot)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???
> >
> >
>
>
>