The way of how I solve my problem... And I don't know how to use IFNULL() so I write this function which works well
CREATE DEFINER=`root`@`%` PROCEDURE `getItemsFromGroup`(IN _groupID INT, IN _magazineID INT)
BEGIN
DECLARE buf VARCHAR(2048); -- buf for parentsID's
DROP TABLE IF EXISTS findParameters; -- table for parameters of group
DROP TABLE IF EXISTS parentIDs; -- __group parents ID
DROP TABLE IF EXISTS itemsValues; -- a EAV table with filtered values
SET @@group_concat_max_len = 10240;
-- ------------------------- --
-- find parameters of group --
-- ----------------------------
IF EXISTS (SELECT * FROM groups WHERE groupID = _groupID) THEN
CREATE TEMPORARY TABLE parentIDs ( id INT) ENGINE=MEMORY;
SELECT GetAncestry(groupID) INTO @buf FROM groups WHERE groupID = _groupID;
SET @values = REPLACE( @buf ,';;', '),(');
SET @values = CONCAT('(', @values, ')');
SET @insert = CONCAT('INSERT INTO parentIDs VALUES', @values);
-- Execute INSERT statement
PREPARE stmt FROM @insert;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Get parameters assigned to parent groups
CREATE TEMPORARY TABLE findParameters ENGINE=MEMORY AS (
SELECT '0' as `itemID` , parameters.parameterID, '0' as `value`, `links_groups-parameters`.parameterOrder as `parameterOrder` FROM parameters
INNER JOIN `links_groups-parameters` ON (`links_groups-parameters`.parameterID = parameters.parameterID )
INNER JOIN parentIDs ON (`links_groups-parameters`.groupID = parentIDs.id)
WHERE parameterInheritance = 1
) ;
-- Get parameters assigned to the current group
INSERT INTO findParameters (`itemID`, parameterID, `value`, parameterOrder)
SELECT '0' , parameters.parameterID, '0', `links_groups-parameters`.parameterOrder FROM parameters
INNER JOIN `links_groups-parameters` ON (`links_groups-parameters`.parameterID = parameters.parameterID )
WHERE (`links_groups-parameters`.groupID = _groupID );
-- Here we have a table with itemID == 0 | parameterID | value | parameterOrder of our interest
-- Now we need to find those parameters in EAV table
-- additional we can obtain a series of interesting data (item number symbols etc.)
CREATE TEMPORARY TABLE itemsValues ENGINE=memory AS (
SELECT items.itemID, magazineRackID, `links_item-values`.parameterID, `links_item-values`.`value`, itemName, itemSymbol FROM `links_item-values`
INNER JOIN items ON (items.itemID = `links_item-values`.itemID)
INNER JOIN groups ON (items.groupID = groups.groupID)
INNER JOIN `links_items-Magazines` ON (`links_items-Magazines`.itemID = items.itemID)
INNER JOIN findParameters ON ( `links_item-values`.parameterID = findParameters.parameterID)
WHERE (groups.groupID = _groupID AND magazineID = _magazineID)
ORDER BY itemID, parameterOrder
);
-- we now got all parameters that are SAVED IN DB if all items have some parameter = NULL (the parameter is not saved in EAV table)
-- now we need to add a "blank item to this table just to provide all parameters of group
INSERT INTO itemsValues (itemID, magazineRackID, parameterID, `value`, itemName, itemSymbol)
SELECT itemID, '0' as magazineRackID, parameterID, `value`, '0' as itemName, '0' as itemSymbol FROM findParameters;
-- getPName returns id;name from parameterID
-- SQL_ESC is a function that prepares string
SET @sql := CONCAT(
" SELECT CONCAT('
SELECT itemID, itemName, itemSymbol, ',
GROUP_CONCAT(DISTINCT CONCAT('
GROUP_CONCAT(IF(",SQL_ESC("parameterID"),"=',QUOTE(",SQL_ESC("parameterID"),"),', ",SQL_ESC("value"),", NULL)) AS ',getPName(",SQL_ESC("parameterID"),"))),'
FROM ", SQL_ESC("itemsValues"),"
GROUP BY itemID
')
INTO @sql
FROM ",SQL_ESC("itemsValues")
);
-- prepare sql query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE IF EXISTS result;
-- execute query (pivot table)
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- do a little clean up
DROP TABLE parentIDs;
DROP TABLE itemsValues;
DROP TABLE findParameters;
-- END IF GROUP EXISTS!!
END IF;
-- do something if groupID is not correct
END
Part that make a pivot table is not my, I just changed it a little bit.
Thank you for help!