Parent / Child Query Assistance

[Albert Frausto]

I have one table that contains the parent / child data for vehicle
makes/models. I'm trying to create the following results which will populate
a listbox. Here's the table:

Table: directory_categories

category_id category_name category_id_parent
1 Acura NULL
2 Ford NULL
3 GM NULL
4 Model T 2
5 Model A 2
6 Truck 2

Target results,

catsub_id catsub_name
1 Acura
2 Ford
4 Ford > Model T
5 Ford > Model A
6 Ford > Truck
3 GM

I've tried the following query but it's not quite giving me the correct
results.

SELECT C1.category_id AS catSub_ID,
C1.category_name+Nz('->'+C2.category_name,'') AS CatSub_Name
FROM directory_categories AS C1 LEFT JOIN directory_categories AS C2 ON
C1.category_id_parent = C2.category_id
ORDER BY C1.category_name;

I get the following with the above query:

cat_SubID cat_SubName
1 Acura
2 Ford
3 GM
4 Model T > Ford (This should be Ford -> Model T)
5 Model A > Ford (This should be Ford -> Model A)
6 Truck > Ford (This should be Ford -> Truck)

Thank you to everyone for their help in advance.

 

[Sylvain Lafontaine]

Pretty obvious:

SELECT C1.category_id AS catSub_ID,
Nz(C2.category_name + '->','') + C1.category_name AS CatSub_Name
FROM directory_categories AS C1 LEFT JOIN directory_categories AS C2 ON
C1.category_id_parent = C2.category_id
ORDER BY Nz(C2.category_name + '->','') + C1.category_name


For the ORDER BY, another solution is also possible:

Order By Case When C2.Category_name is Null Then C1.Category_name Else
C2.Category_name End

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Albert Frausto]

You're correct it was pretty obvious, I was too close to the trees and
couldn't see the forest. Thanks.

Is there another solution that doesn't use the NZ function in the SELECT
statement?

 

[Sylvain Lafontaine]

What's the point of replacing the NZ function?

You could use a Case statement but that would involve testing for Is Null
inside, so it's pretty the same thing and there is also the Coalesce
statement.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Debra]

I have one table that contains the parent / child data for vehicle
makes/models. I'm trying to create the following results which will
populate
a listbox. Here's the table:

Table: directory_categories

category_id category_name category_id_parent
1 Acura NULL
2 Ford NULL
3 GM NULL
4 Model T 2
5 Model A 2
6 Truck 2

Target results,

catsub_id catsub_name
1 Acura
2 Ford
4 Ford > Model T
5 Ford > Model A
6 Ford > Truck
3 GM

I've tried the following query but it's not quite giving me the correct
results.

SELECT C1.category_id AS catSub_ID,
C1.category_name+Nz('->'+C2.category_name,'') AS CatSub_Name
FROM directory_categories AS C1 LEFT JOIN directory_categories AS C2 ON
C1.category_id_parent = C2.category_id
ORDER BY C1.category_name;

I get the following with the above query:

cat_SubID cat_SubName
1 Acura
2 Ford
3 GM
4 Model T > Ford (This should be Ford -> Model T)
5 Model A > Ford (This should be Ford -> Model A)
6 Truck > Ford (This should be Ford -> Truck)

Thank you to everyone for their help in advance.

 

[Debra]

I have one table that contains the parent / child data for vehicle
makes/models. I'm trying to create the following results which will
populate
a listbox. Here's the table:

Table: directory_categories

category_id category_name category_id_parent
1 Acura NULL
2 Ford NULL
3 GM NULL
4 Model T 2
5 Model A 2
6 Truck 2

Target results,

catsub_id catsub_name
1 Acura
2 Ford
4 Ford > Model T
5 Ford > Model A
6 Ford > Truck
3 GM

I've tried the following query but it's not quite giving me the correct
results.

SELECT C1.category_id AS catSub_ID,
C1.category_name+Nz('->'+C2.category_name,'') AS CatSub_Name
FROM directory_categories AS C1 LEFT JOIN directory_categories AS C2 ON
C1.category_id_parent = C2.category_id
ORDER BY C1.category_name;

I get the following with the above query:

cat_SubID cat_SubName
1 Acura
2 Ford
3 GM
4 Model T > Ford (This should be Ford -> Model T)
5 Model A > Ford (This should be Ford -> Model A)
6 Truck > Ford (This should be Ford -> Truck)

Thank you to everyone for their help in advance.