JOIN on same table 2

[Naoise]

CatID | ParentID | Name
1 0 Fruit
2 0 Sports
3 1 Apple
4 1 Orange
5 2 Soccer
6 2 Tennis

How do I query this table to return an ordered table listing a Category and all its children? ie

CatID | ParentID | Name
1 0 Fruit
2 1 Apple
3 1 Orange
4 0 Sports
5 4 Soccer
6 4 Tennis

Just not thinking clearly today

 

[r937]

your example seems to have mysteriously changed the catIDs of several fruits

anyhow, here's how you sort them

ORDER BY
case when parentID = 0
then CatID
else ParentID end
, Name

most people who implement the adjacency model for a hierarchy use NULL as the ParentID of root nodes instead of 0

this would allow the more elegant

ORDER BY
coalesce(parentID,CatID)
, Name





r937.com | rudy.ca

 

[Naoise]

Thanks for the reply and tip.

 

[Naoise]

I have adopted the NULL format that you spoke of and initial tests seemed to work but then it fails for this set of data...

CategoryID ParentCategoryID Name
1 NULL TV
15 NULL Camcorders
16 15 Canon MV750i
18 15 Sony GRDVR10
37 1 Panasonic 24"
38 NULL Domestic Appliances
40 38 Goblin 350 hoover
41 NULL Gadgets
42 41 Zen Creative 10GB
43 41 iRiver 5GB
46 NULL Car
48 NULL Cars

Then I ran
select * from tblCategories order by coalesce(ParentCategoryID, CategoryID)

which returned...

CategoryID ParentCategoryID Name
37 1 Panasonic 24"
1 NULL TV
15 NULL Camcorders
16 15 Canon MV750i
18 15 Sony GRDVR10
38 NULL Domestic Appliances
40 38 Goblin 350 hoover
41 NULL Gadgets
42 41 Zen Creative 10GB
43 41 iRiver 5GB Flash
46 NULL Car
48 NULL Cars

Why is record CategoryID = 37 not below its parent CategoryID 1? Any help appreciated, thanks.

 

[PHV]

ORDER BY coalesce(ParentCategoryID, CategoryID), CategoryID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Thanks, this works

I am confused as to the workings of coalesce. It returns the first nonnull value of the expression list. In this case I would have thought that I wanted the first null value of the expression list to be returned as I want the parent with the null to be returned first and then its children.

Summary :

1)What is actually happening here?
ORDER BY coalesce(ParentCategoryID, CategoryID), CategoryID

2)Is there anyway I can improve this query so that the results returned have the parent categories sorted alphabetically and their children are also sorted alphabetically.

Thanks a bunch for your replies they have been very helpful.

 

[PHV]

SELECT A.CategoryID, A.Name, A.ParentCategoryID, B.Name
FROM tblCategories A LEFT JOIN tblCategories B
ON A.ParentCategoryID = B.CategoryID
ORDER BY coalesce(B.Name, A.Name), coalesce(B.Name, ' ')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, you may want an alias:
SELECT A.CategoryID, A.Name, A.ParentCategoryID, B.Name AS ParentName
FROM tblCategories A LEFT JOIN tblCategories B
ON A.ParentCategoryID = B.CategoryID
ORDER BY coalesce(B.Name, A.Name), coalesce(B.Name, ' ')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

That sorts the parents alright but not the children of the parents. Could you just give me even the briefest of explanations of how the coalesce is operating in this instance? Thanks again.

 

[PHV]

Sorry for the typo:
ORDER BY coalesce(B.Name, A.Name), coalesce(B.Name, ' '), A.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Thank you, that works a treat.