July 9, 2012 at 10:28 pm
Hi,
i have three tables Main Category, Sub Category and Product Category with the sample data as below
Main Category
Mechanical
Heat
Cold
SubCateogry - This table have reference to the Main Category
For Mechanical
ID NameColumn valueColumn
1 Abrasive - 5
2 Puncutre - 10
3 Tear - 15
similarly for the below
For Heat
ID NameColumn valueColumn
4 Extremely Heat - 5
5 Moderate Heat - 10
For Cold
Extremely Cold - 5
Moderate Cold - 10
below Freeze - 15
In the Product category i have
Product A SubcategoryID and Values. i need to display all the subcateogies eventhough it is not having values in the product category table
For ex:
ProductName SubcategoryID value
ProductA 5 2
Even though under heat category it has values for "Moderate Heat" i also need to display the "Extreme Heat" with Hypen as value.
Please provide me suggestion on how to build this query.
July 9, 2012 at 11:26 pm
You'll find a lot more people willing to help you if you post some DDL and sample data in readily consumable format (many regular posters have links in their signatures that tell you how to do this). Since I'm feeling charitable today, I'll show you how:
DECLARE @MC TABLE
(ID INT IDENTITY, Category VARCHAR(20))
INSERT INTO @MC
SELECT 'Mechanical' UNION ALL SELECT 'Heat' UNION ALL SELECT 'Cold'
DECLARE @SC TABLE
(ID INT, NameColumn VARCHAR(20), valueColumn INT, MCID INT)
INSERT INTO @SC
SELECT 1, 'Abrasive', 5, 1
UNION ALL SELECT 2, 'Puncture', 10, 1
UNION ALL SELECT 3, 'Tear', 15, 1
UNION ALL SELECT 4, 'Extremely Heat', 5, 2
UNION ALL SELECT 5, 'Moderate Heat', 10, 2
UNION ALL SELECT 6, 'Extremely Cold', 5, 3
UNION ALL SELECT 7, 'Moderate Cold', 10, 3
UNION ALL SELECT 8, 'below Freeze', 15, 3
DECLARE @Products TABLE
(Name VARCHAR(20), SubCategoryID INT, value INT)
INSERT INTO @Products
SELECT 'ProductA', 5, 2
The query that (I think) will produce your desired results is:
SELECT Name, value, s2.NameColumn
,SCValue=CASE WHEN s2.ID = p.SubCategoryID THEN CAST(s.valueColumn AS VARCHAR) ELSE '-' END
FROM @Products p
INNER JOIN @SC s ON s.ID = p.SubCategoryID
INNER JOIN @SC s2 ON s.MCID = s2.MCID
Results are:
NamevalueNameColumnSCValue
ProductA2Extremely Heat-
ProductA2Moderate Heat10
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 11, 2012 at 1:19 am
Hi,
As it was my first posting i missed out the DDL part. I make sure that on the following postings, i will proivde necessary details to avoid guessing.
Many thanks for providing me the query.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply