related selects for category and subcategory 1

[GUJUm0deL]

Hi all. Need some help here. I have two tables, category and subcategory. I need to do a related selects between these two tables, but am having some difficulties.

The issue I've come across is not all categories will have a sub-category assigned. How can I query the first table to get all categories, and then when the user makes the selection populate the second drop-down with subcategories if any exist for that category.

Thanks!

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[philhege]

What you're looking for is called a LEFT OUTER JOIN. This query returns all records from the "main" table even if no related records exist in the child table. For example,

Categories
ID CategoryName
1 Fruits
2 Vegetables
3 Meats
4 Desserts

SubCategories
SCID CategoryID SubCategoryName
1 1 Apple
2 1 Pear
3 2 Rutabaga
4 2 Tomato
5 4 Pie
6 4 Cake

Your problem is that your query wouldn't return the Meats category. Using the LEFT OUTER JOIN, you are able to get all categories:

SELECT CategoryID, CategoryName, SCID, SubCategoryName
FROM Categories
LEFT OUTER JOIN SubCategories
ON CategoryID = ID

This query results in

CategoryID CategoryName SCID SubCategoryName
1 Fruits 1 Apple
1 Fruits 2 Pear
2 Vegetables 3 Rutabaga
2 Vegetables 4 Tomato
3 Meats NULL NULL
4 Desserts 5 Pie
4 Desserts 6 Cake

See that the query creates a record for the Meats category with NULL field values for the subcategory.

Look in the FAQs for the SQL Server: Programming forum for more resources about using the SQL query language.

HTH,

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[GUJUm0deL]

Awesome. That was so simple. Pretty much all I had to do was change my INNER JOIN statement into LEFT OUTER JOIN.

I can't believe I made such a rookie mistake.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

what's worse is, you're not a rookie ;-)

you could actually use just one table for this

see Categories and Subcategories



r937.com | rudy.ca

 

[philhege]

And shame on me for not considering the poster.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[GUJUm0deL]

Rudy, lol, even pro's make amateur mistakes from time to time, Thanks for the read, will put it to good use in the future.

_____________________________
Just Imagine.

http://www.imajinarts.com