MySQL Forums
Forum List  »  Stored Procedures

Re: Procedure that select different number of columns
Posted by: Zawisza Czarny
Date: October 23, 2013 12:10PM

SELECT  items.itemID, items.itemName, items.itemSymbol, 
		`links_items-Magazines`.itemNumber,`links_items-Magazines`.magazineRackID, 
		group_concat( concat_ws('=', concat(parameters.parameterName, IF ((parameters.parameterUnit=''),'',concat('(',parameters.parameterUnit,')'))  ) ,`links_item-values`.`value` ) SEPARATOR ';;')
FROM items 
INNER JOIN `links_item-values`ON (items.itemID = `links_item-values`.itemID) 
INNER JOIN `links_items-Magazines` ON (items.itemID =`links_items-Magazines`.itemID) 
INNER JOIN parameters ON (`links_item-values`.parameterID = parameters.parameterID)
WHERE groupID = 137  AND ( `links_items-Magazines`.magazineID = 
-- I don't have ID of magazine in my application so I search for one one using unique name of magazine, 
(SELECT magazineID from magazines WHERE magazineName = "magazine name") )
GROUP BY items.itemID
ORDER BY itemID, parameters.parameterID;

The output is something likt that what you proposed (with small modifications)
resistor | SMD0805100K | 321 | resistance(Ω)=100k;;power(W)=1/4 |

group_concat( concat_ws('=', concat(parameters.parameterName, IF ((parameters.parameterUnit=''),'',concat('(',parameters.parameterUnit,')')) ) ,`links_item-values`.`value` ) SEPARATOR ';;')

this code sticks together all parameters and IF a parameter has a defined parameterUnit, adds it to name of parameter. This method is good but has some week points...
As I said before every group in my db has its own set of parameters, but items don't have to have every parameter filled. It would be better if DB retunes all the parameters, even if they are empty.
I have a procedure that returns mi a list of parameters connected to group, but I don't know how to select

resistor ; 321 ; resistance ; 100k
resistor ; 321 ; case ; 0805
resistor ; 321 ; tolerance ; NULL -- If tolerance for this item is not defined, but connected to group
resistor ; 321 ; something ; NULL

and then make

resistor ; 321 ; resistance(Ω)=100k;;case=0805;;tolerance=;;something=

Is there a way to achieve that?

And thanks for info about keys :)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Procedure that select different number of columns
1133
October 23, 2013 12:10PM


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.