0

I have complext data structure similar to functional locations as -- For example, suppose we have the following functional location hierarchy:

1.0 Plant A
    1.1 Building A
        1.1.1 Room 1
        1.1.2 Room 2
    1.2 Building B
        1.2.1 Room 3
        1.2.2 Room 4
2.0 Plant B
    2.1 Building C
        2.1.1 Room 5
        2.1.2 Room 6
    2.2 Building D
        2.2.1 Room 7
        2.2.2 Room 8

I would like to assign logic of additional column with path in binary digits; similar to following --

1.0 Plant A            00000001
    1.1 Building A     00000010
        1.1.1 Room 1  00000100
        1.1.2 Room 2  00001000
    1.2 Building B     00010000
        1.2.1 Room 3  00100000
        1.2.2 Room 4  01000000
2.0 Plant B            10000000
    2.1 Building C     00000001
        2.1.1 Room 5  00000010
        2.1.2 Room 6  00000100
    2.2 Building D     00001000
        2.2.1 Room 7  00010000
        2.2.2 Room 8  00100000

With SQL something like: return all functional locations under Building A:

SELECT * FROM functional_locations WHERE (bitset & 00000010) = 00000010;

Expected result --

1.1 Building A     00000010
1.1.1 Room 1      00000100
1.1.2 Room 2      00001000

The issue is the logic of bitwise AND does not work consistent with levels. I am looking for bit assignment alogrithm for hierarhy that can be achived with above select query.

Following SQLFiddler example

gpa
  • 2,411
  • 6
  • 38
  • 68
  • 3
    Why work with binary? millions of folk around the globe work with hierarchies by just assigning a "parent" column. e.g. select * from hier1 where parent = 56437 and recursive CTEs may also be used. – Paul Maxwell Apr 19 '23 at 04:50
  • I would recommend what they call "Adjacency Model" instead of creating your own scheme, here. You might like this post which outlines other alternatives as well: https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – Josh Apr 19 '23 at 12:50

0 Answers0