Categories and Subcategories Database

Question

I have a problem with retrieve column values like depending on other column I tried to do it with outer join and nested select but I think I can't find the solution in my head ``` Categories Table: ID Name ParentID -------------------------------- 1 Software NULL 2 Domains NULL 3 Games NULL 4 Accounts NULL 5 Others NULL 6 Security Software 1 7 Operating Systems 1 8 Browsers 1 9 Developer Tools 1 10 .com Domains 2 11 .net Domains 2 12 .org Domains 2 13 Online Games 3 14 PC Games 3 15 PS Games 3 16 RapidShare Accounts 4 17 4shared Account 4 18 Web Templates 5 19 Flash Intros 5 20 Firewall 6 21 Antivirus 6 ``` What I want do to display parent category name instead of parentID Like that ``` ID Name ParentID -------------------------------- 1 Software NULL 2 Domains NULL 3 Games NULL 4 Accounts NULL 5 Others NULL 6 Security Software Software 7 Operating Systems Software 8 Browsers Software 9 Developer Tools Software 10 .com Domains Domains 11 .net Domains Domains 12 .org Domains Domains 13…

Accepted Answer

you need to join on the table two times

```
SELECT c1.Id, c1.Name as parentname , c2.name
FROM categories c1
JOIN categories c2
    ON c1.id = c2.parentid
```

the results will be:

```
ID      ParentName            Name
1       software              security software
1       software              operating software
2       domains               .com domains
2       domains               .net domains
2       domains               .org domains
3       games                 pc games
```

which will give you the parentID, parentName and Name of the subcategory or child.

You can also change it to include the subcategory id, if needed.

Based on your Edit you can use the following:

```
create table categories
(
    id int,
    name varchar(50),
    parentid int
)

insert into categories values(1, 'software', null)
insert into categories values(2, 'domains', null)
insert into categories values(3, 'games', null)
insert into categories values(6, 'security software', 1)
insert into categories values(7, 'operating systems', 1)
insert into categories values(8, 'browsers', 1)
insert into categories values(10, '.com domains', 2)
insert into categories values(11, '.net domains', 2)
insert into categories values(12, '.org domains', 2)
insert into categories values(13, 'online games', 3)
insert into categories values(14, 'pc games', 3)
insert into categories values(15, 'ps games', 3)

select c1.id
    , c1.name
    , (select name from categories where c1.parentid = categories.id) as ParentId
from categories c1
```

which will result in:

Categories and Subcategories Database

Solution

Problem