MySQL Forums
Forum List  »  Stored Procedures

Re: Procedure that select different number of columns
Posted by: Zawisza Czarny
Date: October 22, 2013 07:24AM

those are

CREATE TABLE `items` (
`itemID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`itemName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`itemSymbol` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`groupID` int(11) unsigned NOT NULL,
`itemLastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`itemID`),
UNIQUE KEY `itemID_UNIQUE` (`itemID`),
UNIQUE KEY `itemSymbol_UNIQUE` (`itemSymbol`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

1 | resistor   | SMD0805100K | 100 | yyyy-mm-dd
2 | stabilizer | LM7805      | 200 | yyyy-mm-dd

CREATE TABLE `links_item-values` (
`linkID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`itemID` int(11) unsigned NOT NULL,
`parameterID` int(11) unsigned NOT NULL,
`value` char(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`itemID`,`parameterID`),
UNIQUE KEY `linkID_UNIQUE` (`linkID`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 1000 | 1 | 10 | 100k
 1001 | 1 | 11 | 1/4
 2000 | 2 | 20 | 7.5-35
 2001 | 2 | 21 | 5
 2002 | 2 | 22 | 1A5

CREATE TABLE `parameters` (
`parameterID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parameterName` char(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`parameterUnit` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '',
`parameterSingleUnit` char(32) COLLATE utf8_unicode_ci DEFAULT '',
`parameterType` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`parameterID`),
UNIQUE KEY `idparametry_UNIQUE` (`parameterID`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 10 | resistance 
 11 | power
 20 | input voltage 
 21 | output voltage
 22 | max current

CREATE TABLE `magazines` (
`magazineID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`magazineName` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`magazineDesc` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`magazineID`),
UNIQUE KEY `magazineID_UNIQUE` (`magazineID`),
UNIQUE KEY `magazineName_UNIQUE` (`magazineName`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


 1 | area 51 | a special magazine

CREATE TABLE `links_items-Magazines` (
`linkID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`magazineID` int(10) unsigned NOT NULL,
`itemID` int(10) unsigned NOT NULL,
`magazineRackID` char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Not specified',
`itemNumber` double NOT NULL DEFAULT '0',
`elastEdit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`linkID`),
UNIQUE KEY `linkID_UNIQUE` (`linkID`)
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 
 10 | 1 | 1 | hall B | 321 | yyyy.mm.dd
 11 | 1 | 2 | hall X | 2   | yyyy.mm.dd

And the ideal output should be

ITEM NAME | ITEM SYMBOL | ITEMS IN MAGAZINE | parameter 1 ... |

e.g.

name     |  symbol     | number | resistance | power 
resistor | SMD0805100K |  321   | 100k       | 1/4   

or

name       | symbol | number | input voltage | output voltage | max current
stabilizer | LM7805 | 2      |  7.5-35       | 5              | 1A5

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Procedure that select different number of columns
1842
October 22, 2013 07:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.