CODEWITHSUNDEEP
mysqlgroup-concat
Bas Matthee
Bas Matthee

Reputation: 116

Nested GROUP_CONCAT query doesn't return all values

I have a query with a few subqueries, but the strange thing is that the subqueries won't return the same values as if I execute the queries one by one manually.. At first I used 'IN' inside the queries, but no indexes were used, so I converted them to '='. The results are the same with 'IN' or when I use the converted to '=' variation.

SELECT * 
FROM partners
WHERE id = ( 

SELECT GROUP_CONCAT( partner_id
SEPARATOR  ' OR id = ' ) 
FROM product_feeds
WHERE id = ( 

SELECT GROUP_CONCAT( DISTINCT feed_id
SEPARATOR  ' OR id = ' ) 
FROM product_data
WHERE category_id = ( 

SELECT GROUP_CONCAT( id
SEPARATOR  ' OR category_id = ' ) 
FROM product_categories
WHERE parent_id = ( 

SELECT GROUP_CONCAT( id
SEPARATOR  ' OR parent_id = ' ) 
FROM product_categories
WHERE parent_id =1 ) 

ORDER BY NULL ) 

ORDER BY NULL ) 

ORDER BY NULL )

When I, for example, execute the deepest nested 3 subqueries manually, I get 10,11,12,33,34,35 as the final result. When I execute the full 3 subqueries at once, they return 10,11,12.

I am missing results..

Upvotes: 0

Views: 934

Answers (1)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Instead of trying to rely on GROUP_CONCAT, this is a job for INNER JOIN to get results from multiple tables where relationships exist.

SELECT 
    -- Best to specify the precise fields you want here instead of *
    *

FROM partners p

INNER JOIN product_feeds pf
ON pf.partner_id = p.id

INNER JOIN product_data pd
ON pd.feed_id = pf.id

INNER JOIN product_categories pc
ON pc.id = pd.category_id

INNER JOIN product_categories pcparent
ON pcparent.id = pc.parent_id
AND pcparent.parent_id = 1

Upvotes: 3

Related Questions