Mam taką bazę danych:
ss_links | links_id, links_link, links_fkIdModel, links_fkIdShop
ss_models | models_id, models_name, models_fkIdBrand
ss_prices | prices_id, prices_date, prices_price, prices_fkIdLink
ss_shops | shop_id, shop_name, shop_order, shop_query
i chciałbym otrzymać taką tabelę:
___________|shop_name |*coś |shop_name|links_link...
models_name |prices_price|links_link
.
.
.
*coś - coś po czym będę mógł rozpoznać to w c#
Na chwilę obecną mam coś takiego, ale nie działa do końca:
SET @@group_concat_max_len = 50000;
SET @sql_dynamic = (
SELECT
GROUP_CONCAT(DISTINCT
CONCAT("MAX(IF(shop_id ="
,shop_id
,", prices_price, 0)) AS ",shop_name
,",IF(="
,links_link
)
)
FROM ss_prices
LEFT JOIN ss_links ON prices_fkIdLink=links_id
LEFT JOIN ss_models ON links_fkIdModel=models_id
LEFT JOIN ss_shops ON links_fkIdShop=shop_id
);
SET @sql = CONCAT("SELECT models_name AS modelName, ", @sql_dynamic, " FROM ss_prices
LEFT JOIN ss_links ON prices_fkIdLink=links_id
LEFT JOIN ss_models ON links_fkIdModel=models_id
LEFT JOIN ss_shops ON links_fkIdShop=shop_id
WHERE prices_date = '2017-01-18'
GROUP BY models_id;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;