Help with sql statement.

Posted on 2011-10-07
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
    LVL 32

    Accepted Solution

    select ProductName from Products P Inner Join Category C on P.catID = C.IntID
    Where C.IntID = 1 OR C.catParentID = 1
    LVL 51

    Expert Comment

    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 51

    Expert Comment

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

    Author Comment

    Hi Daniel,

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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    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.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now