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.

 

[Frausto]

The reason for replacing NZ function is because I'm using ADO to display the
results in a drop-down menu list using ASP and the NZ function is not
recognized.

Thanks again for all your help.

 

[Sylvain Lafontaine]

The reason for replacing NZ function is because I'm using ADO to display

the
results in a drop-down menu list using ASP and the NZ function is not
recognized.

Without seeing the exact code that you are using, it's impossible to say
what's happening here but in all case, I'm very surprised to hear that the
execution of the Nz() function on the SQL-Server will be dependant on the
data communication interface used. Whatever you are using ADO or DAO with
either ODBC or OLEDB, the execution of the NZ() function should be
independant of that; so I'm suspecting that you are making some sort of
other error.

--
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)