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 :)