From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Need a SQL to create sets of hobbies
Date: Wed, 20 Sep 2006 13:18:39 +0800
Message-ID: <1158729519.6421.271361727@webmail.messagingengine.com> (raw)
Hi!
CREATE TABLE x1 (name TEXT, hobby TEXT, PRIMARY KEY(name,hobby));
INSERT INTO x1 VALUES('John','music');
INSERT INTO x1 VALUES('John','arts');
INSERT INTO x1 VALUES('Bob','arts');
INSERT INTO x1 VALUES('Bob','music');
INSERT INTO x1 VALUES('Rocky','copmputer');
INSERT INTO x1 VALUES('Steve','arts');
INSERT INTO x1 VALUES('Steve','football');
INSERT INTO x1 VALUES('Tom','computer');
INSERT INTO x1 VALUES('Tom','music');
select * from x1;
name | hobby
-------+----------
John | music
John | arts
Bob | arts
Bob | music
Rocky | computer
Steve | arts
Steve | football
Tom | computer
Tom | music
(9 rows)
John and Bob have the same hobbies - music and arts. So music and arts
are treated as one set of hobbies.
Rocky has an unique set of interest - computer.
Steve also likes arts just as John and Bob do, but he also has an
exclusive interest - football. Thus, his set of hobbies is unique -
arts, football.
One of Tom's hobbies, music, overlaps those of John, Bob, and Rocky; but
computer does not. Hence his hobbies, computer and music, forms a new
set of hobbies.
Now we have 4 sets of hobbies:
set 1: music, arts
set 2: computer
set 3: arts, football
set 4: computer, music
I am looking for an SQL that creates sets of hobbies in table x2 by
selecting from table x1:
CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));
and makes x2 contain rows:
sid | hobby
-------+----------
1 | music
1 | arts
2 | computer
3 | arts
3 | football
4 | computer
4 | music
where gid starts from 1.
Thank you in advance!
CN
--
http://www.fastmail.fm - A no graphics, no pop-ups email service
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cnliou9@fastmail.fm
Subject: Re: Need a SQL to create sets of hobbies
In-Reply-To: <1158729519.6421.271361727@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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