RE: Script to retrieve data

Rather than thinking about "jumping to the next product", think in terms
of just copying the details of each product if it is not null. Something
like this should work fine: -

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;

INSERT INTO `invoice_detail`
SELECT InvoiceNo, ProductID01, Quantity01, UnitPrice01, Piece01
FROM invoice
WHERE ProductID01 IS NOT NULL;

INSERT INTO `invoice_detail`
SELECT InvoiceNo, ProductID02, Quantity02, UnitPrice02, Piece02
FROM invoice
WHERE ProductID02 IS NOT NULL; 

INSERT INTO `invoice_detail`
SELECT InvoiceNo, ProductID03, Quantity03, UnitPrice03, Piece03
FROM invoice
WHERE ProductID03 IS NOT NULL;

InvoiceNo will no longer be a primary key in this new table, you might
want to add one, either an extra autonumber field or perhaps concatenate
InvoiceNo and ProductID.

Hope this helps.

John Bonnett

-----Original Message-----
From: ascll [mailto:[email protected]] 
Sent: Tuesday, 31 October 2006 11:06 PM
To: [email protected]
Subject: 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]