CODEWITHSUNDEEP
sql-serverstored-procedures
Shane
Shane

Reputation: 542

Select multiple rows in table from another row that contains multiple values separated by commas

I have one table that contains Titles and Tag ID's called TaggedTitles. I have a second table that contains Tags and TagID's. The TaggedTitles table has multiple tags that are associated with each title. For example:

TaggedTitles table: the TagID column associated with the title 'Automobiles' contains: 1,5,7 that represent tags in the Tags table. In this example 1,5,7 represent tags: wheels,paint,carpet. I need to left join the Tags table to return wheels,paint,carpet using the title Automobiles as a parameter.

SELECT  
    Tags.TAGS
FROM 
    Tags
LEFT JOIN 
    TaggedTitles ON TaggedTitles.TAGID = Tags.TAGID
WHERE 
    TaggedTitles.TITLE = @Title

TitleTable 

TAG_ID  TITLE       
-------------
  1     FRUIT       
  2     FRUIT       
  3     FRUIT

Tags table 

TAG_ID  TAG     
----------------------
  1     ORANGES     
  2     APPLES      
  3     PEARS

Result needed: Oranges,Apples,Pears from parameter 'Fruit'

Upvotes: 1

Views: 90

Answers (1)

arce.est
arce.est

Reputation: 379

Try combine XML PATH with STUFF sentence.

For Example:

DECLARE @INFO TABLE (TAG_ID int, TITLE varchar(10)) 

INSERT @INFO VALUES (1,'ORANGES')
INSERT @INFO VALUES (2,'APPLES')
INSERT @INFO VALUES (3,'PEARS')

SELECT STUFF((
       SELECT ','+TITLE
       FROM @INFO
       FOR XML PATH('')
),1,1, '') AS FRUITS

Upvotes: 1

Related Questions