I have a table:
| Subcat_id | Uppercat_id | Name |
|---|---|---|
| 45 | 12 | Room 1 |
Query:
Select Subcat_id, Uppercat_id, Name
From Rooms
Where:
- Subcat_id is PK
- Name column has names for Subcat_id and Uppercat_id
- ID 45 - Room 1
- ID 12 - Conference Rooms
My goal is to get both Subcat_id nad Uppercat_id's names at the same time like this
| Subcat_id | Uppercat_id | Name (Subcat_id) | Name2 (Uppercat_id) |
|---|---|---|---|
| 45 | 12 | Room 1 | Conference Rooms |
CodePudding user response：
If you store a hierarchy of rooms in the table and you'd like to get the information about the upper level, then you can join the table with itself:
select subcat_level.subcat_id, subcat_level.name, subcat_level.uppercat_id,
upper_level.name
from rooms as subcat_level
left join rooms upper_level on upper_level.subcat_id = subcat_level.uppercat_id