Is there a good reason why you are storing multiple values (comma delimited) in a single string field (id_delim_path)? What you're trying to do would be very trivial if you had stored the individual values in another table such as:

ID Path
477 223
447 323
447 123

I don't know of a built in function in MySQL that will basically do the equivalent of 'split'. You might be able to create a user defined function using existing string functions to do so.

The problem is that your `id_delim_path` column is probably of type CHAR or VARCHAR. After the SELECT subquery one (1) line is returned with one string containing the three integer values separated by a comma. MySQL sees this whole string field as one element from a set. As your `id` field is an integer, MySQL does a type conversion of the (VAR)CHAR value and '223,323,123' becomes 223. The other integers are ignored with the string to integer conversion. That is why it seems that your query is operating on the set (223) only.

The IN operator works only on fields of type SET, or on multi row SELECT results where each row returned by the SELECT is passed as a separate set value to the IN operator. Unfortunately there is no direct type conversion possible with the internal CAST() function of MySQL to convert your (VAR)CHAR column to a set and pass that set to the IN operator, so you probably have to use two separate MySQL statements with some PHP processing inbetween.

ZydoSEO:

I use it this way, because the path can go from between none, to 9+ (for example 123,234,1235,43,4567,324...etc)

Lammert:Thank you, I will not be able to avoid multiple call to the server then in this case.

I could not use the Nested Set Model, due to needing to work with our original software developers, and can only rely on The Adjacency List Model

As my list does have a parentid->currentid model, I can do a self join like this.

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Limitation is ofcourse you'll have to code as many left joins, as your maximum depth. Performance-wise, if a tree only have a depth of 1 or 2, would the other left joins be un-necessary Load to the query?