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