CREATE TABLE NAME (
SELECT name, work
FROM A
UNION ALL
SELECT name, home
FROM A
WHERE home IS NOT NULL
);
CREATE TABLE ADDRESS (
SELECT work
FROM A
UNION ALL
SELECT home
FROM A
WHERE home IS NOT NULL
);
Cheers
/rudy
-----Original Message-----
From: Colt Brunton [mailto:[EMAIL PROTECTED]
Sent: donderdag 17 juli 2003 16:54
To: [EMAIL PROTECTED]
Subject: Help!!!
Hi
I am trying to extract two lots of information from one table but in
such a way as to maintain the relationship between the two entities.
I.E.:
Table A
ID NAME WORK HOME
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1 Jon A null
2 Tracy A null
3 Agnes A B
4 Tom C null
5 Pete X J
6 Steve Y G
7 Jim Z W
8 Mary Z null
9 Sue Z R
10 Lynn K null
11 Tracy H I
12 W
13 M
14 N
15 O
What I need to do is:
1.Get the data held in the fields: "WORK" and "HOME" into one table
(removing duplications)
2.Provide a reference to an address for each "NAME".
Hopefully producing something like:
ADDRESS
^^^^^^^^^^^^^
A
B
C
G
H
I
J
K
M
N
O
X
Y
Z
and separately:
NAME
^^^^^^^^
Jon A
Tracy A
Agnes A
Agnes B
Tom C
Pete X
Pete J
Steve Y
Steve G
Jim Z
Jim W
Mary Z
Sue Z
Sue R
Lynn K
Tracy H
Tracy I
W
M
N
O
I don't even know where to start.....
Regards
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
