Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AF3CF633837 for ; Fri, 22 May 2009 11:54:15 -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 65107-04 for ; Fri, 22 May 2009 11:54:02 -0300 (ADT) X-Greylist: delayed 00:06:40.792094 by SQLgrey-1.7.6 Received: from web27908.mail.ukl.yahoo.com (web27908.mail.ukl.yahoo.com [217.146.182.58]) by mail.postgresql.org (Postfix) with SMTP id ACDD1632B29 for ; Fri, 22 May 2009 11:54:11 -0300 (ADT) Received: (qmail 48334 invoked by uid 60001); 22 May 2009 14:47:28 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1243003648; bh=Wdt+a8755CxXrNhjfeKUmaF1xWO0VBqyjaar+GJ8gss=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=bXO3s5/q8tVQHWRuZe+47IX7/1rKMMe+tfhsV5R+cTVO9A2ARNBY5s48B4//Zf7UAiFpBPFzrup5I5/HNuwrgGWkKnwV0s9qOt/r3gl9tDVft3PTS/2UunhuqyzQfzZA5lsiWvkCdDkWIHFc1v/UcbhFkAU8opuM3vASs61jJ/4= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=tNhc2rhROHhkUhs01p7RKormG21jvdpu5ji5RTJnmYdIIfiYFDWcVSVqU6o9IICisUZriSDcjDvxQhAvrkqPkk5xDlo8rTAw7iOXr94j2OcN0lRdPwxmd/0C1EAaEjzXdqnsGzKehSkx6PODt5LL+FmrQ9rZr6ccOM3GMQhW9kY=; Message-ID: <200454.48323.qm@web27908.mail.ukl.yahoo.com> X-YMail-OSG: TirzU0wVM1mt62TbgXfHICPogGCBJxFabG0Kp_mwxEVVduzrhM17t_DKCTqjyd2tdP9amGMbybY_7yMSKZmVxajP0bzBozMO8syqMUo8L448n1PzWHGfuiXj9rvB0jXLBcV3qYRL8JwXcDcJPWQdQFraD8ZG6cpr8Fnxiuwp49nGyXnwAhpbr49jF96jHrOuvUYgfcZCCmxfBLXY79NCcKim3dQDmE3BeXbwEDrI0ecLZYc_JLEIUpxZendgxrQv03HQA_rkP_uAUdsEFg-- Received: from [196.46.105.168] by web27908.mail.ukl.yahoo.com via HTTP; Fri, 22 May 2009 14:47:27 GMT X-Mailer: YahooMailClassic/5.3.9 YahooMailWebService/0.7.289.10 Date: Fri, 22 May 2009 14:47:27 +0000 (GMT) From: James Kitambara Subject: Re: Need help combining 2 tables together To: Richard Ekblom Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-145040660-1243003647=:48323" 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/95 X-Sequence-Number: 32587 --0-145040660-1243003647=:48323 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Dear Richard Ekblom, I think Mr. Adrian Klaver gave you the solution. Mine is the similar soluti= on SELECT message.id,topic.topic,message.message=20 FROM topics, messages=20 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 favorite 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 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=A0=A0=A0 Fax: +255 22 2130852, Mobile : +255 71 3307632, 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 tables into one. > First, please take a look at the following table setups... > > CREATE TABLE topics ( >=A0 =A0 id SERIAL PRIMARY KEY, >=A0 =A0 topic TEXT NOT NULL > ); > > CREATE TABLE messages ( >=A0 =A0 id SERIAL PRIMARY KEY, >=A0 =A0 topic INTEGER REFERENCES topics(id), >=A0 =A0 message TEXT NOT NULL > ); > > Example of a topics table: > ID=A0 =A0 TOPIC > 1=A0 =A0=A0=A0Are squares better then circles? > 2=A0 =A0=A0=A0My favorite food > > Example 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 > > 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=A0=A0=A0TOPIC=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0=A0=A0MESSAGE > 1=A0 =A0 My favorite food=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 I like l= asagna! > 2=A0 =A0 My favorite food=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Pizza is= also a favorite > 3=A0 =A0 Are squares better then circles?=A0 =A0 I like circles, they rem= ind 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??? > >=0A=0A=0A --0-145040660-1243003647=:48323 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
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 mess= age.id;
 
After executing this query you will get the following:

id |          &n= bsp;   topic         = ;      |       =   message
----+----------------------------------+-----------------= ---------
  1 | My favorite food      = ;           | I like lasa= gna!
  2 | My favorite food      &nbs= p;          | Pizza is also a = favorite
  3 | Are squares better then circles? | I like circles, t= hey
           &n= bsp;            = ;            &n= bsp;  : remind me of pizza
 
Best Regards,
 
Muhoji James Kita= mbara
Database Administ= rator,
B.Sc. With Comput= er Science and Statistics (Hons),
National Bureau o= f Statistics,
P.O. Box 796,
Tel : +255 22 212= 2722/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 wrot= e:
> Hello
>
> I have frequently encountered the need of = combining two tables into one.
> First, please take a look at the fol= lowing table setups...
>
> CREATE TABLE topics (
>  =   id SERIAL PRIMARY KEY,
>    topic TEXT NOT NULL
&= gt; );
>
> CREATE TABLE messages (
>    id SERI= AL PRIMARY KEY,
>    topic INTEGER REFERENCES topics(id),>    message TEXT NOT NULL
> );
>
> Exampl= e of a topics table:
> ID    TOPIC
> 1   &n= bsp; Are squares better then circles?
> 2    &nbs= p;My favorite food
>
> Example of a messages table:
> ID&= nbsp;   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 combi= ne these tables with a single SELECT to get...
>
> Combined tab= le:
> ID   TOPIC          &nb= sp;                   &nb= sp;MESSAGE
> 1    My favorite food      &nbs= p;             I like lasagna!
> 2 = ;   My favorite food              &= nbsp;     Pizza is also a favorite
> 3    Are squ= ares better then circles?    I like circles, they remind me
&g= t; 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 p= roduce exactly the combined table above???
>
>

=0A=0A=0A=0A --0-145040660-1243003647=:48323--