I've got a temporary (this is important, as I cannot open it twice on the same query to make JOIN
) table with the following structure:
CREATE TEMPORARY TABLE `temp_report_tags` (
`report_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`report_title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`attr_value_id` bigint(20) NOT NULL,
`attr_category_id` bigint(20) NOT NULL,
`attribute_count` bigint(21) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
With some sample data:
+-----------+--------------+---------------+------------------+-----------------+
| report_id | report_title | attr_value_id | attr_category_id | attribute_count |
+-----------+--------------+---------------+------------------+-----------------+
| 1 | SEN vs PUR | 16 | 2 | 3 |
| 1 | SEN vs PUR | 34 | 3 | 43 |
| 2 | PRA vs TLS | 35 | 1 | 48 |
| 2 | PRA vs TLS | 36 | 2 | 51 |
| 2 | PRA vs TLS | 37 | 2 | 4 |
| 2 | PRA vs TLS | 51 | 3 | 5 |
+-----------+--------------+---------------+------------------+-----------------+
What I need
I need to get the value_id
concatenated according to categories, only if the report has all of the needed categories the user wants.
So, in the given example, if the user says "I want to see reports by category_id
1, 2 and 3", I'd need to show the following:
+------------+-----------+--------------+
| value_tree | report_id | report_title |
+------------+-----------+--------------+
| 35,36,51 | 2 | PRA vs TLS |
| 35,37,51 | 2 | PRA vs TLS |
+------------+-----------+--------------+
As you can see, as report with ID 1 does not have category 1 it must be hidden from the result set, and as report with ID 2 has two values for category 2, it has to show two results, one with each tree of combinations.
If it had three values for category_id = 3
, then I should get all the tree possible combinations for it.
I'm using PHP too, so a solution with parsing data and working with it can be also a correct one.
I've been trying to work with GROUP_CONCAT()
, HAVING COUNT(category_id)
and ORDER BY FIELD(category_id)
, but I haven't been able to make it to appear a result for every possible combination, as I cannot use JOINs.
Thanks everyone for your help and your time!