Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 39E1A635EC0 for ; Fri, 22 May 2009 12:33:08 -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 75349-03-5 for ; Fri, 22 May 2009 12:33:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 785BF635A15 for ; Fri, 22 May 2009 12:31:40 -0300 (ADT) Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id K0H2PFKM; Fri, 22 May 2009 16:31:37 +0100 Message-ID: <01c601c9daf2$65dc0a70$ec5a3d0a@marktestcr.marktest.pt> From: "Oliveiros Cristina" To: "James Kitambara" , "Richard Ekblom" Cc: References: <200454.48323.qm@web27908.mail.ukl.yahoo.com> Subject: Re: Need help combining 2 tables together Date: Fri, 22 May 2009 16:31:38 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01C3_01C9DAFA.C77A4CD0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 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/97 X-Sequence-Number: 32589 This is a multi-part message in MIME format. ------=_NextPart_000_01C3_01C9DAFA.C77A4CD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I guess this is pretty much the same=20 as doing=20 SELECT message.id,topic.topic,message.message=20 FROM topics=20 JOIN messages=20 ON topics.id =3D message.topic ORDER BY message.ID Ain't I right? Best, Oliveiros ----- Original Message -----=20 From: James Kitambara=20 To: Richard Ekblom=20 Cc: pgsql-sql@postgresql.org=20 Sent: Friday, May 22, 2009 3:47 PM Subject: Re: [SQL] Need help combining 2 tables together Dear Richard Ekblom, I think Mr. Adrian Klaver gave you the solution. Mine is the = similar solution SELECT message.id,topic.topic,message.message=20 FROM topics, messages=20 WHERE message.topic=3Dtopic.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,=20 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??? > >=20 ------=_NextPart_000_01C3_01C9DAFA.C77A4CD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I guess this is pretty much the same =
as doing
 SELECT=20 message.id,topic.topic,message.message
FROM topics
JOIN messages
ON topics.id =3D message.topic
ORDER BY message.ID
 
Ain't I right?
 
Best,
Oliveiros
 
----- Original Message -----
From:=20 James Kitambara
Sent: Friday, May 22, 2009 3:47 = PM
Subject: Re: [SQL] Need help = combining 2=20 tables together

Dear Richard Ekblom,

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

id=20 = |            =  =20 = topic           &n= bsp;  =20 |        =20 = message
----+----------------------------------+----------------------= ----
 =20 1 | My favorite=20 = food           &nb= sp;    =20 | I like lasagna!
  2 | My favorite=20 = food           &nb= sp;    =20 | Pizza is also a favorite
  3 | Are squares better then = circles? | I like circles,=20 = they
           = ;            =             &= nbsp;  =20 : remind me of pizza
 
Best Regards,
 
Muhoji = James=20 Kitambara
Database=20 Administrator,
B.Sc. = With=20 Computer Science and Statistics (Hons),
National Bureau of=20 Statistics,
P.O. = Box 796,=20
Tel : = +255 22=20 2122722/3    Fax: +255 22 = 2130852,
Mobile = : +255 71=20 3307632,
Dar es = Salaam,
Tanzania.

 
-----------------------------------------ORGINAL=20 MESSAGE--------------------------------
On Friday 22 May 2009 6:48:43 am = Richard Ekblom=20 wrote:
> Hello
>
> I have frequently = encountered the=20 need of combining two tables into one.
> First, please = take a=20 look at the following table setups...
>
> CREATE = TABLE=20 topics (
>    id SERIAL PRIMARY = KEY,
> =20   topic TEXT NOT NULL
> );
>
> CREATE = TABLE=20 messages (
>    id SERIAL PRIMARY = KEY,
> =20   topic INTEGER REFERENCES topics(id),
>  =  =20 message TEXT NOT NULL
> );
>
> Example of a = topics=20 table:
> ID    TOPIC
> 1 =20    Are squares better then circles?
> = 2 =20    My favorite food
>
> Example of = a=20 messages table:
> ID    TOPIC   =20 MESSAGE
> 1     2    =    =20 I like lasagna!
> 2     2  =  =20     Pizza is also a favorite
> 3 =20    1        I like circles, = they=20 remind me of pizza
>
> Notice that the number of = topics=20 may differ from the number of messages.
>
> Now I = want to=20 combine these tables with a single SELECT to = get...
>
>=20 Combined table:
> ID   TOPIC    =  =20                   =  =20      MESSAGE
> 1    My = favorite=20 food                =  =20   I like lasagna!
> 2    My favorite = food =20                   = Pizza=20 is also a favorite
> 3    Are squares better = then=20 circles?    I like circles, they remind me
> = of=20 pizza
>
> I have seen different examples of this = with=20 something called JOIN but
> they always give me only two = rows.=20 How can I do this when the two tables
> may have = different sizes=20 to produce exactly the combined table=20 = above???
>
>

= ------=_NextPart_000_01C3_01C9DAFA.C77A4CD0--