Joining SQL Server Tables

Question

Movie

MovieID

Title

with values:

• 1, 'Underworld Evolution'
• 2, 'Taken'

Category

CategoryId

Name

with values:

• 1, 'Action'
• 2, 'Horror'
• 3, 'Drama'
• 4, 'Suspense'

The association table:

MovieCategory

• MovieId
• MovieCategoryId

with values:

MovieId    CategoryId

    1                        1
    1                        2
    2                        1
    2                        4

Is there a way which I can make an output like this by joining those three tables:

MovieId    Title                                Category
1                Underworld Evolution      Action,Horror
2                Taken                             Action,Suspense

(select

    a.Store,
    a.Id,
    a.Title,
    a.Publisher,
    a.SerialNo,
    max(a.cat1) + max(a.cat2) + max(a.cat3) + max(a.cat4) + max(a.cat5) Genre,
    a.Rate,
    a.ReleaseDate,
    a.MovieStatus

    from

        (select 

        m.Id,
        m.Title,
        p.Name Publisher,

        case 
            when mc.Description like 'Action' then mc.Description + ', ' else '' end cat1,
        case 
            when mc.Description like 'Comedy' then mc.Description + ', ' else '' end cat2,
        case 
            when mc.Description like 'Drama' then mc.Description + ', ' else '' end cat3,
        case 
            when mc.Description like 'Horror' then mc.Description + ', ' else '' end cat4,
        case 
            when mc.Description like 'Suspense' then mc.Description + ', ' else '' end cat5,

        mis.SerialNo,
        mr.Description Rate,
        m.ReleaseDate,
        ms.Description MovieStatus,
        s.Name Store

        from MovieCategoryMovie mcm

        inner join Movie m
        on mcm.MovieId like m.Id

        inner join MovieCategory mc
        on mc.Id like mcm.MovieCategoryId

        inner join Publisher p
        on m.PublisherId like p.Id

        inner join MovieRate mr
        on m.MovieRateId like mr.Id

        inner join MovieInStore mis
        on mis.MovieId like m.Id

        inner join Store s
        on mis.StoreId like s.Id

        inner join MovieStatus ms
        on ms.Id like mis.MovieStatusId

        where s.Name like @store

        group by

        m.Id,
        m.Title,
        mcm.MovieId,
        mcm.MovieCategoryId,
        p.Name,
        mr.Description,
        m.ReleaseDate,
        mc.Description,
        ms.Description,
        s.Name,
        mis.SerialNo

        ) as a

This code is from my stored procedure that does this output which I'm talking about.

I've already tried this using case statements but I don't know if it will be the only way to do it, because I think it's not quite looking good with it. Hope somebody can help me. Thanks!

Answer 1

SELECT
     [MovieID], [Title],
     STUFF(
         (SELECT ', ' + b.[Title]
          FROM MovieCategory a
               INNER JOIN Category b
                  On a.CategoryID = b.MovieID
          WHERE  a.[MovieID] = d.[MovieID]
          FOR XML PATH ('')) 
          , 1, 1, '')  AS CategoryList
FROM Movie AS d
GROUP BY [MovieID], [Title]

• SQLFiddle Demo