What you want for that is something like this:
CREATE TABLE #Categories
INSERT INTO #Categories
VALUES (1, 'Cosmetics'),
CREATE TABLE #Products
INSERT INTO #Products
VALUES (1, 'Chair'),
CREATE TABLE #Product_Categories (Prod_ID INT, Cat_ID INT);
INSERT INTO #Product_Categories
VALUES (1, 3),
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('.', 'varchar(1000)'), 1, 1, '') AS Categories
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