Script to retrieve data

Hi,

Due to the previous poor database design, now I having
trouble to create a "master-detail" relationship for
existing table with many records. The worst case is I
do NOT allow to change table structure because some
customised applications are based on exisiting
structures.

My job is to develop a small application to work
together with their exisiting applications. Therefore,
I need to extract data from 1 of their existing table
to my own new table for my own processing.


EXISTING TABLE
==============

CREATE TABLE `invoice` (
  `InvoiceNo` int(8) unsigned NOT NULL default '0',

  `ProductID01` varchar(31) default NULL,
  `Quantity01` float unsigned default '0',
  `UnitPrice01` decimal(10,2) default '0.00',
  `Piece01` varchar(15) default NULL,

  `ProductID02` varchar(31) default NULL,
  `Quantity02` float unsigned default '0',
  `UnitPrice02` decimal(10,2) default '0.00',
  `Piece02` varchar(15) default NULL,

  `ProductID03` varchar(31) default NULL,
  `Quantity03` float unsigned default '0',
  `UnitPrice03` decimal(10,2) default '0.00',
  `Piece03` varchar(15) default NULL,

  PRIMARY KEY (`InvoiceNo`)
) TYPE=MyISAM


NEW TABLE
=========
CREATE TABLE `invoice_detail` (
  `InvoiceNo` int(8) unsigned NOT NULL default '0',
  
  `ProductID` varchar(31) default NULL,
  `Quantity` float unsigned default '0',
  `UnitPrice` decimal(10,2) default '0.00',
  `Piece` varchar(15) default NULL,

  KEY (`InvoiceNo`)
) TYPE=MyISAM

My question is, how to I extract all record from
"invoice" to my "invoice_detail" in master-detail
manner if: -

 - for each InvoiceNo, if ProductID01 is NOT null, I
will copy ProductID01, Quantity01, UnitPrice01 and
Piece01 to "invoice_detail" and then jump to
ProductID02, else, not copy and jump to next InvoiceNo
 
 - if ProductID02 is NOT null, I will copy
ProductID02, Quantity02, UnitPrice02 and Piece02 to
"invoice_detail" and then jump to ProductID03, else,
not copy and jump to next InvoiceNo
 
 - if ProductID03 is NOT null, I will copy
ProductID03, Quantity03, UnitPrice03 and Piece03 to
"invoice_detail", else, not copy and jump to next
InvoiceNo


Could someone show me the script in order for me to
"migrate" the data from "invoice" to my
"invoice_detail"?

Thanks in advance and I'm using MySQL ver 4.0.2X.


=====
ascll


 
____________________________________________________________________________________
Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates 
(http://voice.yahoo.com)


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]