COALESCE returns duplicate rows

[sgietz]

I have a database for a local restaurant guide. Here are some sample tables:

TABLE RESTAURANTS

restaurant_id ¦ restaurant_name
-------------------------------
1 ¦ Billy Bob's Burgers

===============================

TABLE CUISINES

cuisine_id ¦ cuisine_name
-------------------------
1 ¦ Mexican
2 ¦ American

===============================

LOOKUP TABLE

cuisine_id ¦ restaurant_id
--------------------------
1 ¦ 1
2 ¦ 1

OK, a quick explanation ... The first table holds restaurants, the second table holds cuisines, and because each restaurant may be associated with more than one cuisine, I have the third table here.

On one of the pages I want this result set:

restaurant_name ¦ cuisine_names
-------------------------------
Billy Bob's ¦ American, Mexican

I have the comma separated values coming in fine using a UDF. First, here's the procedure:

SELECT
restaurant_name,
food.concatCuisines(food.restaurants.restaurant_id) AS clist
FROM food.restaurants
INNER JOIN food.restaurant_cuisine
ON food.restaurants.restaurant_id = food.restaurant_cuisine.restaurant_id
INNER JOIN food.cuisines
ON food.restaurant_cuisine.cuisine_id = food.cuisines.cuisine_id
ORDER BY restaurant_name

And here's the function:

DECLARE @Output VARCHAR(8000)

SELECT @Output = COALESCE(@Output + ', ', '') + c.cuisine_name
FROM food.cuisines c
LEFT JOIN food.restaurant_cuisine rc
ON c.cuisine_id = rc.cuisine_id
WHERE rc.restaurant_id = @id

RETURN @Output

Here's what I'm getting:

restaurant_name ¦ cuisine_names
-------------------------------
Billy Bob's ¦ American, Mexican
Billy Bob's ¦ American, Mexican

I don't understand why I get two rows. What can I do to get just the one row?

Thanks :)

[sgietz]

I figured it out. If you can guess what I did, feel free to call me a moron for overlooking the obvious ;)