Re: Need help combining 2 tables together

-----------------------------------------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???
>
>