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

tobzzz
tobzzz used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nathan RileyFounder
Commented:
Your asking about how to do it without selecting the feature_name and get 1 row?  how about this
SELECT adv.advertid,adv.advert_title
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
group by adv.advertid,adv.advert_title

Open in new window

Commented:

You should be able to run this using the table structures you provided, however I haven't test it so please let me know if there are errors, thanks


select d.advertid, d.advert_title, g.features
from advert_details d
cross apply (select
                ( select f.feature_name + ',' as 'data()'
                  from advert_features f
                  join feature_names n on n.id = f.featureid
                  where f.advid = f.advertid
                  for xml path ('')
                ) as features) g

Author

Commented:
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.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
@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!

Author

Commented:
@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!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial