Message-Id: <1158729519.6421.271361727@webmail.messagingengine.com>
From: "CN" <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="Big5"
MIME-Version: 1.0
Subject: Need a SQL to create sets of hobbies
Date: Wed, 20 Sep 2006 13:18:39 +0800

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