asked on

Help with sql statement.

I need help creating a sql statement. Given the below 2 tables. I am aware that this would have been easier if a subcategory table would have been created instead of stroring the categories and subcategories together.

Table Category
intID catParentID CatName
1 NULL Name 1
2 NULL Name 2
3 NULL Name 3
5 2 Name 4
7 2 Name 5
8 1 Name 6

Table Products
IntID catID ProductName
1 8 Prd 1
2 8 Prd 2
3 5 Prd 3
4 7 Prd 4
5 1 Prd 5
6 3 Prd 6

So if i select intID 1 from the category table it will return Prd 1, Prd 2, and Prd 5 from the products table.

So if i select intID 2 from the category table it will return Prd 3 and Prd 4 from the products table.

So if i select intID 3 from the category table it will return Prd 6 from the products table.

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

HainKurt

do you have only one level of subcategory? or is there no limit on subcategories?
for examplke a record in

Table Category
intID catParentID CatName
...
9 8 Name 7
10 8 Name 8

HainKurt

if yes you need to implement "Recursive Queries Using Common Table Expressions"

http://msdn.microsoft.com/en-us/library/ms186243.aspx

techpr0

ASKER

Hi Daniel,

Thanks for the help. I opened up another question which is basically doing this backwards. Can you help?
https://www.experts-exchange.com/questions/27386118/Help-with-Sql-Statement.html