pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help combining 2 tables together
5+ messages / 5 participants
[nested] [flat]
* Need help combining 2 tables together
@ 2009-05-22 13:48 Richard Ekblom <ens01rem@cs.umu.se>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Ekblom @ 2009-05-22 13:48 UTC (permalink / raw)
To: pgsql-sql
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???
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help combining 2 tables together
@ 2009-05-22 14:17 Adrian Klaver <aklaver@comcast.net>
parent: Richard Ekblom <ens01rem@cs.umu.se>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2009-05-22 14:17 UTC (permalink / raw)
To: pgsql-sql; +Cc: Richard Ekblom <ens01rem@cs.umu.se>
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???
>
>
> 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
aklaver@comcast.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help combining 2 tables together
@ 2009-05-22 14:47 James Kitambara <jameskitambara@yahoo.co.uk>
0 siblings, 2 replies; 5+ messages in thread
From: James Kitambara @ 2009-05-22 14:47 UTC (permalink / raw)
To: Richard Ekblom <ens01rem@cs.umu.se>; +Cc: pgsql-sql
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???
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help combining 2 tables together
@ 2009-05-22 15:31 Oliveiros Cristina <oliveiros.cristina@marktest.pt>
parent: James Kitambara <jameskitambara@yahoo.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Oliveiros Cristina @ 2009-05-22 15:31 UTC (permalink / raw)
To: James Kitambara <jameskitambara@yahoo.co.uk>; Richard Ekblom <ens01rem@cs.umu.se>; +Cc: pgsql-sql
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/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???
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help combining 2 tables together
@ 2009-05-22 15:39 Rob Sargent <robjsargent@gmail.com>
parent: James Kitambara <jameskitambara@yahoo.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2009-05-22 15:39 UTC (permalink / raw)
To: pgsql-sql; +Cc: James Kitambara <jameskitambara@yahoo.co.uk>
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
> 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???
> >
> >
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-05-22 15:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-22 13:48 Need help combining 2 tables together Richard Ekblom <ens01rem@cs.umu.se>
2009-05-22 14:17 ` Adrian Klaver <aklaver@comcast.net>
2009-05-22 14:47 Re: Need help combining 2 tables together James Kitambara <jameskitambara@yahoo.co.uk>
2009-05-22 15:31 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-22 15:39 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox