MySQL Forums
Forum List  »  General

Re: mysql pivot dynamic tables and return only where count > 0
Posted by: Christian Nuvoli
Date: March 29, 2016 10:14AM

here's the complete query:

$user_attrib = some_value;
$user_size = some_value;
$items = [218, 219];
$items = join(",", $items);



SELECT owned_items.model_id,
       Count(owned_items.model_size_id) AS total_owned,
       makes.make,
       items.model_name,
       items.picture,
       items.color,
       items.material,
       Count(CASE item_sizes.id WHEN '1' THEN item_sizes.id ELSE NULL END) AS '1',
       Count(CASE item_sizes.id WHEN '2' THEN item_sizes.id ELSE NULL END) AS '2',
       Count(CASE item_sizes.id WHEN '3' THEN item_sizes.id ELSE NULL END) AS '3',
       Count(CASE item_sizes.id WHEN '4' THEN item_sizes.id ELSE NULL END) AS '4',
       Count(CASE item_sizes.id WHEN '5' THEN item_sizes.id ELSE NULL END) AS '5',
       Count(CASE item_sizes.id WHEN '6' THEN item_sizes.id ELSE NULL END) AS '6',
       Count(CASE item_sizes.id WHEN '7' THEN item_sizes.id ELSE NULL END) AS '7',
       Count(CASE item_sizes.id WHEN '8' THEN item_sizes.id ELSE NULL END) AS '8',
       Count(CASE item_sizes.id WHEN '9' THEN item_sizes.id ELSE NULL END) AS '9',
       Count(CASE item_sizes.id WHEN '10' THEN item_sizes.id ELSE NULL END) AS '10',
       Count(CASE item_sizes.id WHEN '11' THEN item_sizes.id ELSE NULL END) AS '11',
       Count(CASE item_sizes.id WHEN '12' THEN item_sizes.id ELSE NULL END) AS '12',
       Count(CASE item_sizes.id WHEN '13' THEN item_sizes.id ELSE NULL END) AS '13',
       Count(CASE item_sizes.id WHEN '14' THEN item_sizes.id ELSE NULL END) AS '14',
       Count(CASE item_sizes.id WHEN '15' THEN item_sizes.id ELSE NULL END) AS '15'
FROM   owned_items
       INNER JOIN owned_item_user
               ON owned_items.id = owned_item_user.owned_item_id
       INNER JOIN users
               ON owned_item_user.user_id = users.id
       INNER JOIN item_sizes
               ON owned_items.model_size_id = item_sizes.id
       INNER JOIN items
               ON owned_items.model_id = items.id
       INNER JOIN makes
               ON owned_items.make_id = makes.id      
WHERE  users.attrib_id = $user_attrib
       AND users.size_id = $user_size
       AND owned_items.model_id IN ($items)
GROUP  BY owned_items.model_id

Options: ReplyQuote


Subject
Written By
Posted
Re: mysql pivot dynamic tables and return only where count > 0
March 29, 2016 10:14AM


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.