February 6, 2010 at 4:02 am
I have 3 table Join.
because i can't drawing table in text editor of forum, i attach file bellow
Thank any idea.
February 6, 2010 at 4:33 am
This is rather simplistic.
Select pav.ProductAttributeValueID,pc.CategoryName, pca.ProductCategoryAttributeName,pav.AttributeValue
From ProductAttributeValue pav
Inner Join ProductCategoryAttribute pca
On pca.productcategoryattributeid = pav.productcategoryattributeid
Inner Join ProductCategory pc
On pc.ProductCategoryID = pca.ProductCategoryID
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 6, 2010 at 12:23 pm
Thank CirquedeSQLeil
your post is not true with my case
when ProductCategoryID=4
it must get all child of ProductCategoryID =4
but when i input ProductCategoryID = 4
it get only Mobile phone
February 6, 2010 at 12:26 pm
fotechvnu (2/6/2010)
Thank CirquedeSQLeilyour post is not true with my case
when ProductCategoryID=4
it must get all child of ProductCategoryID =4
but when i input ProductCategoryID = 4
it get only Mobile phone
I don't follow what you are saying. When setting up the data you provided - i got accurate results for your requirements.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 6, 2010 at 12:29 pm
Select pav.ProductAttributeValueID,pc.CategoryName, pca.ProductCategoryAttributeName,pav.AttributeValue
From ProductAttributeValue pav
Inner Join ProductCategoryAttribute pca
On pca.productcategoryattributeid = pav.productcategoryattributeid
Inner Join ProductCategory pc
On pc.ProductCategoryID = pca.ProductCategoryID
where pc.ProductCategoryID = 4
You might not understand my thought?
can you try!
February 6, 2010 at 12:33 pm
I see what you are saying now.
A recursive CTE using the query I provided you inside the cte should get that for you. Just use the parentcategory to map to the productcategory in your recursion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 6, 2010 at 12:55 pm
this is DB i using.
You can setup it and re-check my case.
Thank for reply
In this DB, if i run with your idea
when ProductCategoryID=6, nothing show.
sorry! my english not good.
February 7, 2010 at 6:58 am
Please help me!!
February 7, 2010 at 9:37 am
Another option
declare @productcategoryid
set @productcategoryid = 4
Select pav.ProductAttributeValueID,pc.CategoryName, pca.ProductCategoryAttributeName,pav.AttributeValue
From ProductAttributeValue pav
Inner Join ProductCategoryAttribute pca
On pca.productcategoryattributeid = pav.productcategoryattributeid
Inner Join ProductCategory pc
On pc.ProductCategoryID = pca.ProductCategoryID
Where pc.parentcategoryid = @productcategoryid
or pc.productcategoryid = @productcategoryid
another option
Add a left outer join to the select that I showed you. This join is a self join from pc to productcategory on productcategoryid = parentcategoryid
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2010 at 10:56 pm
sorry for making you hard
but actually the way you made did not meet my problem
can refer to the following example and continue to help me
in this example, I get all children of a parent category.
but I still can not get from table AttributeValue ProductAttributeValue
as I think, it is not as simple as you think. you can see the example I did, from which you can expand more
February 9, 2010 at 11:28 am
You will need to provide the data in a readily consumable format (the data that you are using) and the table structures. Your query is accessing different attributes/columns than what you specified in your initial doc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 3:49 pm
Sorry
I have the right to change data structures have posted
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply