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.

techpr0Asked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:
select ProductName from Products P Inner Join Category C on P.catID = C.IntID
Where C.IntID = 1 OR C.catParentID = 1
0
 
HainKurtSr. System AnalystCommented:
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
0
 
HainKurtSr. System AnalystCommented:
if yes you need to implement "Recursive Queries Using Common Table Expressions"

http://msdn.microsoft.com/en-us/library/ms186243.aspx
0
 
techpr0Author Commented:
Hi Daniel,

Thanks for the help. I opened up another question which is basically doing this backwards. Can you help?
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27386118.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.