How to retrieve data from two tables related using a third table, SQL Server

Question

I have three tables(simplified)

movie(id int primary key identity, title varchar(20) not null)
genre(id int primary key identity, type varchar(10) not null)

movie_genre(movie_id int references movie(id), 
            genre_id int references genre(id), 
            primary key(movie_id, genre_id))

Data in movie

id          title
---------------------------
1     |     Inception
2     |     The Dark Knight

Data in genre

id          type 
---------------------
1     |     action 
2     |     adventure 
3     |     thriller

Data in movie_genre

movie_id          genre_id 
----------------------------
1               |     1 
1               |     2 
2               |     1 
2               |     3

I want to display movie name with its genre types displayed in one column. So, the output would be

title              |     genres 
-----------------------------------------
Inception          |     action adventure 
The Dark Knight    |     action thriller

I tried to do it in this way

select 
    movie.title, genre.type 
from 
    movie, genre 
where 
    movie.id = movie_genre.movie_id 
    and genre.id = movie_genre.genre_id;

but it says :

The multi-part identifier "movie_genre.movie_id" could not be bound.
The multi-part identifier "movie_genre.genre_id" could not be bound.

I am very new to SQL, any help would be appreciated.

Edit :
Using

SELECT G.[Type] ,M.[Title]
FROM movie_genre MG  
LEFT JOIN genre G ON MG.genre_id = G.ID
LEFT JOIN movie M ON MG.Movie_ID = M.ID

OR

select movie.title, genre.type 
from movie, genre, movie_genre
where
movie.id = movie_genre.movie_id 
and genre.id = movie_genre.genre_id;

The output is now,

    title              |     genres 
-----------------------------------------
Inception          |     action
Inception          |     adventure 
The Dark Knight    |     action
The Dark Knight    |     thriller

How could I display genres in one row?

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Aug 23, 2014 at 15:16
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 23, 2014 at 15:22
I have yet to learn what is inner join, outer join, left join etc. So, I am not using them for simplicity. thanks anyways. — Praveen Kumar Sirohiwal
– Praveen Kumar Sirohiwal, Commented Aug 23, 2014 at 15:32
Learn them - right now. Stop using stuff that's been deprecated for over 20 years. — marc_s
– marc_s, Commented Aug 23, 2014 at 20:15

M.Ali · Accepted Answer · 2014-08-23 15:34:18Z

SELECT G.[Type] 
       ,M.[Title]
FROM movie_genre MG  
LEFT JOIN genre G ON MG.genre_id = G.ID
LEFT JOIN movie M ON MG.Movie_ID = M.ID

To get a list

SELECT DISTINCT M.[Title]
      ,STUFF((
           SELECT ' ' + G.[Type] 
           FROM genre G INNER JOIN movie_genre MG
           ON  MG.genre_id = G.ID
           WHERE MG.Movie_id = Mov.Movie_id
           FOR XML PATH(''),TYPE)
            .value('.','NVARCHAR(MAX)'),1,1, '') Genre
FROM movie_genre Mov  
INNER JOIN movie M ON Mov.Movie_ID = M.ID

OR

SELECT DISTINCT M.[Title]
      ,STUFF(List,1,1, '') Genre
FROM @movie_genre Mov  
INNER JOIN @movie M 
ON Mov.Movie_ID = M.ID
             CROSS APPLY 
                       (
                       SELECT ' ' + G.[Type] 
                       FROM @genre G INNER JOIN @movie_genre MG
                       ON  MG.genre_id = G.ID
                       WHERE MG.Movie_id = Mov.Movie_id
                       FOR XML PATH('')
                       )Gen(List)

`SQL FIDDLE`

fiddledsticks · Accepted Answer · 2014-08-23 15:10:11Z

-2

I believe you will need to add the 'movie_genre' to FROM, e.g:

SELECT movie.title, genre.type FROM (movie, genre, movie_genre) WHERE ....

answered Aug 23, 2014 at 15:10

fiddledsticks

71 bronze badge

1 Comment

Vadim Ovchinnikov Over a year ago

It's not valid SQL syntax.

Collectives™ on Stack Overflow

How to retrieve data from two tables related using a third table, SQL Server

2 Answers 2

To get a list

OR

`SQL FIDDLE`

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

To get a list

OR

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related