Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 19514633C91 for ; Fri, 22 May 2009 12:39:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 77248-05 for ; Fri, 22 May 2009 12:39:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f180.google.com (mail-qy0-f180.google.com [209.85.221.180]) by mail.postgresql.org (Postfix) with ESMTP id 03F54633CB8 for ; Fri, 22 May 2009 12:39:21 -0300 (ADT) Received: by qyk10 with SMTP id 10so456231qyk.15 for ; Fri, 22 May 2009 08:39:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=Ub9V6HYAYHAYXLi2YYhei3XEPK9MLngFSgle06Ro++Q=; b=QTJ63rcVyVo7rxEHxjieN1rsB2s8Z23Ob12R8OST+kVt4eAeehjCeiQnCVopJcSg36 lvTpQes/1Ikb/TWoFrJ80N1ZtP1Lv0EWMWA1fi838MXR4ryr9xdP5n8gfdX3JRIRxcm+ 0ohJNxkUORdQ4r65khEGlOn65IVt/kfEME64s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=DkxMtHwMnACK9ZPUVL6FICZ6AtQ/3gX63SsOGtRAC5sMjsO5mw3WDkDbWVoQ++YcmE fy6oMGcyUWwTkosh/BBEOGT/TRbuWHmQuC+IuysNOrtY1FOTCNvFCUvLQllbN7DrM7v5 V+M+r7iwJtXRRtq/b+Dzkr0uLN9BH2SrwC1jo= MIME-Version: 1.0 Received: by 10.224.2.74 with SMTP id 10mr4023524qai.259.1243006749354; Fri, 22 May 2009 08:39:09 -0700 (PDT) In-Reply-To: <200454.48323.qm@web27908.mail.ukl.yahoo.com> References: <200454.48323.qm@web27908.mail.ukl.yahoo.com> Date: Fri, 22 May 2009 09:39:09 -0600 Message-ID: <5c4ddc540905220839g2dd6a032k19c4ba3c28e1a602@mail.gmail.com> Subject: Re: Need help combining 2 tables together From: Rob Sargent To: pgsql-sql@postgresql.org Cc: James Kitambara Content-Type: multipart/alternative; boundary=0015175cd614bfb133046a820f76 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/98 X-Sequence-Number: 32590 --0015175cd614bfb133046a820f76 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 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??? > > > > > > > --0015175cd614bfb133046a820f76 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable if you want topics listed which don't yet have messages try

sele= ct t.id, t.topic, m.id<= /a>, m.message from topics t left join messages m on m.topic =3D t.id;

On Fri, May 22, 2009 at 8:47 AM, James Kitam= bara <ja= meskitambara@yahoo.co.uk> wrote:
Dear Richard Ekblom,

I think Mr. Adrian Klaver gave you the solution. Mine is the simil= ar solution
SELECT message.id,= topic.topic,message.message
FROM topics, messages
WHERE message.topic=3Dtopic.id order by message.id;
=A0
After executing this query you will get the following:

id |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 topic=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0 message
----+-----= -----------------------------+--------------------------
=A0 1 | My favo= rite food=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | I like lasagna!=
=A0 2 | My favorite food=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 | Pizza is also a favorite
=A0 3 | Are squares better then circles? | I like circles, they
=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 : remind me of pizza
=A0
Best Regards,
=A0
Muhoji James Kitamba= ra
Database Administrat= or,
B.Sc. With Computer = Science and Statistics (Hons),
National Bureau of S= tatistics,
P.O. Box 796,
Tel : +255 22 212272= 2/3=A0=A0=A0 Fax: +255 22 2130852,
Mobile : +255 71 330= 7632,
Dar es Salaam,
Tanzania.=
=A0
-----------------------------------------ORGINAL MESSAGE--------------= ------------------
On Friday 22 May 2009 6:48:43 am Richard Ekblom wrote:
> Hello>
> I have frequently encountered the need of combining two tabl= es into one.
> First, please take a look at the following table setup= s...
>
> CREATE TABLE topics (
>=A0 =A0 id SERIAL PRIMARY KEY,>=A0 =A0 topic TEXT NOT NULL
> );
>
> CREATE TABLE me= ssages (
>=A0 =A0 id SERIAL PRIMARY KEY,
>=A0 =A0 topic INTEGER= REFERENCES topics(id),
>=A0 =A0 message TEXT NOT NULL
> );
>
> Example of a t= opics table:
> ID=A0 =A0 TOPIC
> 1=A0 =A0=A0=A0Are squares bett= er then circles?
> 2=A0 =A0=A0=A0My favorite food
>
> Exa= mple of a messages table:
> ID=A0 =A0 TOPIC=A0 =A0 MESSAGE
> 1=A0 =A0=A0=A02=A0 =A0 =A0 =A0 = I like lasagna!
> 2=A0 =A0=A0=A02=A0 =A0 =A0 =A0 Pizza is also a favorite
> 3=A0 =A0=A0=A01= =A0 =A0 =A0 =A0 I like circles, they remind me of pizza
>
> Not= ice that the number of topics may differ from the number of messages.
&g= t;
> Now I want to combine these tables with a single SELECT to get..= .
>
> Combined table:
> ID=A0=A0=A0TOPIC=A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A0MESSAGE
> 1=A0 =A0 My fa= vorite food=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 I like lasagna!
> = 2=A0 =A0 My favorite food=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Pizza is a= lso a favorite
> 3=A0 =A0 Are squares better then circles?=A0 =A0 I like circles, they = remind me
> of pizza
>
> I have seen different examples o= f 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 p= roduce exactly the combined table above???
>
>


--0015175cd614bfb133046a820f76--