asked on

SQL select statement joining two tables but returning one row with a field comma separating info from second table

I want to return one row only for a lookup. At the moment it's returning 5. Please see example and explanation below.

Note, i'm using SQL Server 2008 express.

table called 'advert_details'

advertid	|  advert_title
----------------------------
    1       | puzzle for sale
----------------------------
    2       | phone for sale
----------------------------

table called 'advert_features'

id	|	advid	|	featureid
--------------------------------------------------------
 1	|	   1	|	   4
--------------------------------------------------------
 2	|	   1	|	   3
--------------------------------------------------------
 3	|	   1	|	   5
--------------------------------------------------------
 4	|	   1	|	   1
--------------------------------------------------------
 5	|	   1	|	   2

table called 'feature_names'

id	|	feature_name
------------------------
 1	|	as new
------------------------
 2	|	free delivery
------------------------
 3	|	gift wrapped
------------------------
 4	|	not made in china
------------------------
 5	|	free return
 
For my example, the SQL I've written is like this:

SELECT adv.advertid,adv.advert_title,fea.feature_name
FROM advert_details adv
	LEFT JOIN advert_features af ON adv.advertid = af.advid
	LEFT JOIN feature_names fea ON af.featureid = fea.id
WHERE adv.advert_title LIKE '%puzzle%' OR af.advid = 3

So the user has searched keyword "puzzle" but also searched for a feature of gift wrapped.

I get 5 rows returned of course and I understand why, but what I want is 1 row, with the feature names comma separated in the last field, so it would look like this:

advertid	|	advert_title	|	feature_name
-------------------------------------------------------------------------------------------------------
1	|	puzzle for sale |	as new, free delivery, gift wrapped, not made in china, free return
	
is this possible? 

If not, is it possible to not return feature_name at all (I could do this in a separate SELECT later) but still search on the same WHERE and only return one row, not five?

Thanks experts - I hope i've been clear!

Open in new window

SOLUTION

Nathan Riley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

jaan33

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tobzzz

ASKER

Thank you both.

@Gallitin: your answer worked but it was for my "if not possible answer", so I'm awarding jaan33 100points more as his fitted what I primarily wanted, comma separating the features.

tobzzz

ASKER

@jaan33: You mixed up some tables there. E.g. where f.advid = f.advertid. However, I was able to see what you were trying to do and the principle so applied it to my code and it worked beautifully. I'd never seen or used cross apply before... very handy!

tobzzz

ASKER

@jaan33: Sorry for a follow-up after awarding the points, but how can I get comma separated id column from the feature_names table instead of the feature_name column?
so not: 1 | puzzle for sale | as new, free delivery, gift wrapped, not made in china, free return
but instead: 1 | puzzle for sale | 1, 2, 3, 4, 5
Thanks!

jaan33

Replace the feature name part with convert(varchar, n.id), sry typing on my phone, tx