potrzebuje pomocy w dokończeniu zapytania bo nie wiem jak mam to wykonać a próby kończą się nie powodzeniem.
W ten sposób pobieram elementy do zapytania:
SELECT GROUP_CONCAT(DISTINCT CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "', c.MSH_PRICES_DATE, '" THEN id ELSE NULL END) AS ', c.MSH_PRICES_DATE)) AS column_list FROM MSH_PRICES c;
zwraca mi to przykladowo:
COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN id ELSE NULL END) AS 2009-08-16, COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN id ELSE NULL END) AS 2009-08-17, COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN id ELSE NULL END) AS 2009-08-18, COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN id ELSE NULL END) AS 2009-08-19, COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN id ELSE NULL END) AS 2009-08-20
chce to wstawic (pierwszy kod) w zapytanie:
SELECT o.MSH_CLASSES_NAME { column_list = kod pierwszy a raczej jego wyniki} FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID GROUP BY o.MSH_CLASSES_NAME
tak się nie da (próbowałem) bo drugi sql zwraca mi bloba:
SELECT ( SELECT o.MSH_CLASSES_NAME FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID ) AS CLASSES,( SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(CASE WHEN c.MSH_PRICES_DATE = "',c.MSH_PRICES_DATE,'" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "',c.MSH_PRICES_DATE,'"')) ) FROM MSH_PRICES c GROUP BY c.MSH_PRICES_MSH_CLASSES_ID LIMIT 0, 30;
koncowy sql musi tak wygladac tylko nie wiem jak za pomoca jednego zapytania do niego dojść (skopiowalem to co wyszło z pierwszego do drugiego sql'a i jest okej)
SELECT (SELECT o.MSH_CLASSES_NAME FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID) AS CLASSES, MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-16", MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-17", MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-18", MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-19", MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-20" FROM MSH_PRICES c GROUP BY c.MSH_PRICES_MSH_CLASSES_ID LIMIT 0, 30;
moze ktoś wie jak to zrobić bo moja wiedza tak daleko nie sięga.
będę wdzięczny.