August 21, 2012 at 9:16 am
Hello everyone,
I am a newbie to sql and I am faced with the problem below.
I have 3 tables
Categories
Cat_ID | Cat_Name
1 | Cosmetics
2 | Hair
3 | Furniture
4 | Office
Products
Prod_ID | Prod_name
1 | Chair
2 | Gel
PRoduct_categories
Prod_ID | Cat_id
1 | 3
1 | 4
2 | 1
2 | 2
I need a query that will give me the following
Product | Categories
Chair | Furniture , Office
Gel | Cosmetics, Hair
Any help would be greatly appreciated
Regards,
Lyndon
August 21, 2012 at 9:29 am
What you want for that is something like this:
CREATE TABLE #Categories
(Cat_ID INT,
Cat_Name VARCHAR(25));
INSERT INTO #Categories
(Cat_ID, Cat_Name)
VALUES (1, 'Cosmetics'),
(2, 'Hair'),
(3, 'Furniture'),
(4, 'Office');
CREATE TABLE #Products
(Prod_ID INT,
Prod_Name VARCHAR(25));
INSERT INTO #Products
(Prod_ID, Prod_Name)
VALUES (1, 'Chair'),
(2, 'Gel');
CREATE TABLE #Product_Categories (Prod_ID INT, Cat_ID INT);
INSERT INTO #Product_Categories
(Prod_ID, Cat_ID)
VALUES (1, 3),
(1, 4),
(2, 1),
(2, 2);
SELECT Prod_Name,
STUFF((SELECT ',' + Cat_Name
FROM #Categories AS C
INNER JOIN #Product_Categories AS PC
ON C.Cat_ID = PC.Cat_ID
WHERE PC.Prod_ID = #Products.Prod_ID
FOR XML PATH(''),
TYPE).value('.[1]', 'varchar(1000)'), 1, 1, '') AS Categories
FROM #Products;
For future reference, it's very helpful on your part if you were to provide table scripts and data like I did. Makes it much easier for us to help you. No big deal in a simple case like this, but it does make it easier.
The way my final query works is:
The outer query just pulls the Prod_Name and Prod_ID from the #Products table. That's simple.
The inline sub-query uses the Prod_ID in the outer query to pull the Cat_Name values from the other two tables (that's the Where clause), then uses a trick with For XML Path to turn the data into a list with commas. If you look up "t-sql for xml path" online, you'll find articles on how to do that and how it works. Then, it finally runs a Stuff() function to get rid of the comma at the beginning of the list.
Sub-queries like this, that use a value from the outer query, are called "correlated sub-queries". You can also search online for that, and you'll find more information about how they work, what they're for, and what rules apply to them.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 22, 2012 at 3:20 am
Thanks a lot GSquared, that worked.
I will keep scripts in mind in future.
Regards,
Lyndon
August 27, 2012 at 1:53 pm
If you can use the SQLCLR here is an option that is a bit more expressive in terms of the syntax:
SELECT p.Prod_Name,
dbo.GROUP_CONCAT(c.Cat_Name)
FROM #Products p
JOIN #Product_Categories pc ON p.Prod_ID = pc.Prod_ID
JOIN #Categories c ON pc.Cat_ID = c.Cat_ID
GROUP BY Prod_Name
You can download the dbo.GROUP_CONCAT function here: http://groupconcat.codeplex.com
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply