I have the following query:
SELECT GROUP_CONCAT(p.name) AS names
FROM cte t
LEFT JOIN products p
ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;
It returns something like this:
+-------------------------------------+
| names |
+-------------------------------------+
| iPhone 14 Promax,Samsung Galaxy A55 |
+-------------------------------------+
But I want the following result:
+----+-------------------------------------+
| id | names |
+----+-------------------------------------+
| 2 | iPhone 14 Promax |
| 3 | Samsung Galaxy A55 |
+----+-------------------------------------+
Any idea how can I get that result?
With the data from your "Online Demo" link (already prepared with the recursion) there is no need for creating a list with Group_Concat() function to get the expected result.
WITH -- S a m p l e D a t a :
RECURSIVE tags AS (
SELECT 1 AS id, 'Home' AS name, NULL AS parent_id UNION ALL
SELECT 2, 'Kitchen', 1 UNION ALL
SELECT 3, 'Serving and reception', 2 UNION ALL
SELECT 4, 'Spoon', 3 UNION ALL
SELECT 5, 'Digital', NULL UNION ALL
SELECT 6, 'Communication', 5 UNION ALL
SELECT 7, 'Cellphone', 6
),
products AS (
SELECT 1 AS id, 'Dinner Spoon Set,16 Pcs 7.3" Tablespoons' AS name, 4 AS tag_id UNION ALL
SELECT 2, 'iPhone 14 Promax', 7 UNION ALL
SELECT 3, 'Samsung Galaxy A55', 7
),
cte (id, name, parent_id, orig_id) AS (
SELECT id, name, parent_id, id AS orig_id
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
FROM tags t1
INNER JOIN cte t2
ON t2.id = t1.parent_id
)
Using same joined objects with the same ON condition as in your code you can select the product id and name directly - without creating the list that should be splitted back again. Since there is a kind of hardcoding in your list generating code ...
...
Having SUM(t.id = 6) > 0
you could do the same in main sql's where clause like bellow (Where t.name = 'Cellphone') or (Where t.id = 7)
-- M a i n S Q L :
Select p.id, p.name
From cte t
Left Join products p ON(p.tag_id = t.id)
Where t.name = 'Cellphone'
-- OR Where t.id = 7
/* R e s u l t :
id name
-- ---------------------
2 iPhone 14 Promax
3 Samsung Galaxy A55 */
However, if you need to use your having clause condition (no metter why) then just use it as a correlated subquery in main sql's Where clause like here:
-- M a i n S Q L :
Select p.id, p.name
From cte t
Left Join products p ON(p.tag_id = t.id)
Where t.id = (Select Max(id)
From cte
Where orig_id = t.orig_id
Group By orig_id
Having Sum(id = 6) > 0)
... result is the same as above ...
See the fiddle here.