0

I found this thread: Finding all parents in mysql table with single query (Recursive Query). It describes how can I easly find all parents of single child.

I'm wondering if can I fetch all parents for multiple children found by query.

Eg. we have a table like bellow:

table1
--------------------
id | name | parent_id
1  | aaa  | null
2  | bbb  | 1 
3  | bbb  | 1
4  | ccc  | 3
5  | ccd  | 1

And we'd like to find all parents for items with name that contains 'cc'

select id from table1 where name like '%cc%';
# ids fetched
# => 4, 5
# parents what we're looking for
# for id = 4 -> 4,3,1 | for 5 -> 5,1

I tried something like this but it didn't work:

SELECT T2.id, T2.name, T2.parent_id, T1._id, T1.lvl
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := l.id from table1 l where name like '%cc%') tmp, 
        table1 t
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id;

Thanks in advance.

Edit because I was not clear enough and I simplified the example as much as possible, so I missed a crucial part of what i really need:

What I need to find is any parent of a related table have a flag set to true. I have 3 tables:

 categories
 ---------------------
 id | name | parent_id | flag

 articles
 ---------------------
 id | name

 # intersection between categories and related articles
 category_articles
 ---------------------
 category_id | article_id

As you can see articles can have multiple categories. I'd like to list articles ordered first by any parent flag asc (true first, false after them), then by name. The 'flag' collumn should be equal to true when one of category related with article or any parent of related to category have flag set to true. Example result:

  articles
  id | name | flag
  1  | aaa  | true
  5  | ccc  | true
  2  | hhh  | true
  3  | bbb  | false
  4  | zzz  | false

I need to do this in one query to apply limit and offset without breaking sorting.

Community
  • 1
  • 1
SZMER
  • 183
  • 2
  • 15

1 Answers1

0

You can use UNION in MySQL to combine the result from multiple select statements into a single result set.

In this case you could do it like this:

select id from table1 where name like '%cc%'
union
select parent_id from table1 where name like '%cc%';
caitriona
  • 8,569
  • 4
  • 32
  • 36
  • This is good answer for my question but it is not what I'm looking for. I'll update question cause i was not clear enough. Anyway thx for your answer. – SZMER Oct 02 '14 at 06:28