CODEWITHSUNDEEP
mysqlsqltreehierarchical-data
Andrew T
Andrew T

Reputation: 357

MySQL - Query Tree of Data?

I have a data set like this in a MySQL database, but it's about 50,000+ records.

A01                   Description
A01.01                Description
A01.02                Description
A01.03                Description
A01.03.01             Description
A01.03.02             Description
A01.03.02.01          Description
A01.03.03             Description
A02                   ....
A02.01
A03
B01
B02
B02.02
B02.03
...

I'd like to do a query where I can pass something like "A" that gets me all the children of but just one level deep, so I get A01, A02, A03, but I also need to know that A01 and A02 have children and A03 does not.

I'll need to repeat the query passing "A01.03" to get A01.03's children and know that A01.03.02 has children but the others do not.

I've got a database of about 50,000+ of these and need to efficiently query it on demand. I can probably change the structure a bit or add flags if needed.

Any tips or ideas would be greatly appreciated! Thanks!

Upvotes: 1

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If I understand correctly, you can do:

select substring_index(t.col1, '.', 1),
       ( count(*) > 1 ) as has_children
from t
where t.col1 like 'A%'
group by substring_index(t.col1, '.', 1);

Upvotes: 3

Related Questions