Help with Sql Statement


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 Products table it will return catName 1 from the Category table

So if i select intID 2 from the Products table it will return catName 1 from the Category table

So if i select intID 3 from the Products table it will return catName 2 from the Category table

So if i select intID 4 from the Products table it will return catName 2 from the Category table

So if i select intID 5 from the Products table it will return catName 1 from the Category table

So if i select intID 6 from the Products table it will return catName 3 from the Category table



 
techpr0Asked:
Who is Participating?
 
jogosCommented:
select products.*, isnull(category.catParentID,category.IntId)
from products
inner join category on products.CatId = category.IntId
0
 
Daniel WilsonCommented:
This one is more complex ... but I think this will do it for you.

Select Coalesce(C2.CatName, C.CatName) from Products P Inner Join Category C
on P.CatID = C.intID LEFT Join Category C2 on C2.intID = C.catParentID
0
 
Paul MacDonaldDirector, Information SystemsCommented:
As I read your tables, your scenarios are all wrong.  Selecting intID 1 from the Products table should return catName 6 (because catID = 8 for both).  The first three of your examples are off.  Correct me if I'm wrong.
0
 
jogosCommented:
@paulmacd
The column name catParentId ... that will make the link.
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.