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@yahoo.co.uk> wrote:
Dear Richard Ekblom, I think Mr. Adrian Klaver gave you the solution. Mine is the similar solution
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??? > >
|