[SQL] Need help combining 2 tables together

[Richard Ekblom]

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:
IDTOPIC
1 Are squares better then circles?
2 My favorite food

Example of a messages table:
IDTOPICMESSAGE
1 2I like lasagna!
2 2Pizza is also a favorite
3 1I 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
1My favorite foodI like lasagna!
2My favorite foodPizza is also a favorite
3Are 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???



Some SQL for Postgres if you want to set up this example...

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);

INSERT INTO topics(topic) VALUES('Are squares better then circles?');
INSERT INTO topics(topic) VALUES('My favorite food');
INSERT INTO messages(topic,message) VALUES(2, 'I like lasagna!');
INSERT INTO messages(topic,message) VALUES(2, 'Pizza is also a favorite');
INSERT INTO messages(topic,message) VALUES(1, 'I like circles, they 
remind me of pizza');

SELECT * FROM topics;
SELECT * FROM messages;


Thanks in advance
/RE


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Need help combining 2 tables together

[Adrian Klaver]

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:
 IDTOPIC
 1 Are squares better then circles?
 2 My favorite food

 Example of a messages table:
 IDTOPICMESSAGE
 1 2I like lasagna!
 2 2Pizza is also a favorite
 3 1I 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
 1My favorite foodI like lasagna!
 2My favorite foodPizza is also a favorite
 3Are 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???


 Some SQL for Postgres if you want to set up this example...

 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);
 INSERT INTO topics(topic) VALUES('Are squares better then circles?');
 INSERT INTO topics(topic) VALUES('My favorite food');
 INSERT INTO messages(topic,message) VALUES(2, 'I like lasagna!');
 INSERT INTO messages(topic,message) VALUES(2, 'Pizza is also a favorite');
 INSERT INTO messages(topic,message) VALUES(1, 'I like circles, they
 remind me of pizza');
 SELECT * FROM topics;
 SELECT * FROM messages;


 Thanks in advance
 /RE

test=# SELECT m.id,t.topic,m.message from topics as t,messages as m where 
m.topic=t.id order by m.id;
 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


-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Need help combining 2 tables together

[Oliveiros Cristina]

I guess this is pretty much the same 
as doing 
 SELECT message.id,topic.topic,message.message 
FROM topics 
JOIN messages 
ON topics.id = message.topic
ORDER BY message.ID

Ain't I right?

Best,
Oliveiros

  - Original Message - 
  From: James Kitambara 
  To: Richard Ekblom 
  Cc: pgsql-sql@postgresql.org 
  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 
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/3Fax: +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:
   IDTOPIC
   1 Are squares better then circles?
   2 My favorite food
  
   Example of a messages table:
   IDTOPICMESSAGE
   1 2I like lasagna!
   2 2Pizza is also a favorite
   3 1I 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
   1My favorite foodI like lasagna!
   2My favorite foodPizza is also a favorite
   3Are 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???
  
   

Re: [SQL] Need help combining 2 tables together

[Rob Sargent]

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 jameskitamb...@yahoo.co.uk
 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/3Fax: +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:
  IDTOPIC
  1 Are squares better then circles?
  2 My favorite food
 
  Example of a messages table:
  IDTOPICMESSAGE
  1 2I like lasagna!
  2 2Pizza is also a favorite
  3 1I 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
  1My favorite foodI like lasagna!
  2My favorite foodPizza is also a favorite
  3Are 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???