0

I am trying to get result from my categories table using the parent path i have created. When i launch the request WHERE IN with manual data it's working perfectly. When i am trying the same request dynamically with subquery, i got only one result instead of 4 expected. I do not understand why, can you help me ?

http://sqlfiddle.com/#!2/88b68/6

/*Working query*/
SELECT t.id_categorie FROM t 
WHERE t.id_categorie IN (1396,1399,1403,1412)

/*Not working by subquery ??*/
SELECT cat.id_categorie FROM t as cat
WHERE 
cat.id_categorie IN (SELECT REPLACE(t.path,'.',',') FROM t WHERE t.id_categorie = 1412)

Thanks by advance,

Regards,

lwillems
  • 103
  • 13
  • That's because `t.path` is a `varchar` column and not `INT`. – Rahul Jun 13 '14 at 11:38
  • See FIND_IN_SET(), although this kind of problem can be symptomatic of poor design. That said, materialized paths are an accepted strategy. – Strawberry Jun 13 '14 at 12:24

2 Answers2

1

Using in with a comma delimited list does not do what you want. Even when you use a subquery. You could try this:

SELECT cat.id_categorie
FROM t cat
WHERE EXISTS (SELECT 1
              FROM t
              WHERE t.id_categorie = 1412 AND
                    find_in_set(cat.id_categorie, REPLACE(t.path, '.', ',')) > 0
             );

I'm not sure what your data looks like or even what this query is supposed to be doing (it seems strange that the subquery is on the same table as the outer query). Usually, though, you want to store things in tables rather than delimited lists. You see, SQL has a built-in data structure for lists. It is called a table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

The reason this does not work is that your inner SELECT produces a single comma-separated VARCHAR, rather than four individual integers.

In MySQL you can use find_in_set, like this:

SELECT cat.id_categorie FROM t as cat
WHERE find_in_set(cat.id_categorie, (
  SELECT REPLACE(t.path,'.',',')
  FROM t WHERE t.id_categorie = 1412))

What I did was replacing the IN expression in your query with a call to find_in_set, leaving everything else the same.

Demo

However, this solution is fundamentally flawed, because it stores multiple values in a single column to represent a many-to-many relationship. A better approach to implementing this requirement is using a separate table that connects rows of the table to their related rows.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • Thanks i understand :-) I use this solution to get Breadcrumb instead of recursive query, can you explain a little more the better practice suggested ? – lwillems Jun 13 '14 at 12:00
  • @user3384179 If I correctly understand what you are trying to achieve, check out [answers to this question](http://stackoverflow.com/q/18603372/335858), they show how to do implement a relationship-to-self in MySQL. – Sergey Kalinichenko Jun 13 '14 at 12:32