CODEWITHSUNDEEP
mysqlsql
AuthenticReplica
AuthenticReplica

Reputation: 870

Fetching several values from the same column in a table

I have 2 tables. One that lists all objects (object) and one that contains the hierarchy for the objects (hierarchy).

Object table:
ID   | Description | ClassID
------------------------------
0001 | Room 1A     | 1000
0002 | Floor 1     | 1001
0003 | Room 1B     | 1000
0004 | Building 01 | 1002

Where classID specifies the type of object (room=1000, floor=1001 or building=1002)

Hierarchy table:
RelationID | parentid | child ID | level
1          | 0004     | 0002     |   1
2          | 0004     | 0001     |   2
3          | 0002     | 0001     |   1

Where level specifies the hierarchical level of the child. E.g. if I'm searching all buildings then (1 --> child (floor) , 2--> grandchild (room)).

So I would like to fetch the description from the object table for all the rooms, the floor and the building they are in.

E.g.

Room | Floor | Bldg
-------------------
1A   |   1   |  01
1B   |   1   |  01
2A   |   2   |  01
2B   |   2   |  01
1A   |   1   |  02

etc. How can I go about doing this?

Upvotes: 1

Views: 43

Answers (2)

ughai
ughai

Reputation: 9880

You can join ObjectTable and HierarchyTable multiple times like this.

SELECT B.Description, F.Description, R.Description
FROM ObjectTable B
    INNER JOIN HierarchyTable HT1
        ON B.ID = HT1.parentid
    INNER JOIN ObjectTable F
        ON F.ID = HT1.childid
    INNER JOIN HierarchyTable HT2
        ON HT1.childid = HB2.parentid
    INNER JOIN ObjectTable R
        ON R.ID = HT2.childid
WHERE B.ClassID = 1002
    AND F.ClassID=1001
    AND R.ClassID =1000
    AND HT1.level = 1
    AND HT2.level = 1

EDIT

Only filter Level = 1 is required. grandchild records from hierarchy is not needed as we traverse child to child

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

You can use such query to get the data:

SELECT Room.Description as Room, Floor.Description as Floor, Buildings.Description as Bldg
FROM ObjectTable Buildings 
    INNER JOIN Hierarchy BuildingFloor ON Buildings.ID = BuildingFloor.ParentId AND BuildingFloor.level = 1
    INNER JOIN ObjectTable Floor ON BuildingFloor.ChildId = Floor.ID
    INNER JOIN Hierarchy FloorRoom ON Floor.ID = FloorRoom.ParentId AND FloorRoom.level = 2
    INNER JOIN ObjectTable Room ON FloorRoom.ChildId = Room.ID
WHERE Buildings.ClassID = 1002 AND Floors.ClassID=1001 AND Rooms.ClassID =1000

I don't fully understand where and how you use level field, so this part of query might be not needed

Upvotes: 1

Related Questions