Help with sql statement.

Posted on 2011-10-07
Medium Priority
Last Modified: 2012-05-12
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.

Question by:techpr0
  • 2
LVL 32

Accepted Solution

Daniel Wilson earned 2000 total points
ID: 36932928
select ProductName from Products P Inner Join Category C on P.catID = C.IntID
Where C.IntID = 1 OR C.catParentID = 1
LVL 61

Expert Comment

ID: 36933000
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
LVL 61

Expert Comment

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


Author Comment

ID: 36933254
Hi Daniel,

Thanks for the help. I opened up another question which is basically doing this backwards. Can you help?

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question