The result Im getting after joining tables
| MovieId | MovieName | Actor Name | ProducerName | Date | Plot |
|---|---|---|---|---|---|
| 1 | hulk | abe | don | 2021-09-24 | smash |
| 1 | hulk | link | don | 2021-09-24 | smash |
| 1 | hulk | abe | kal | 2021-09-24 | smash |
| 1 | hulk | link | kal | 2021-09-24 | smash |
How to get the result as follows in SQL Server
| MovieId | MovieName | ActorName | ProducerName | Date | Plot |
|---|---|---|---|---|---|
| 1 | hulk | abe,link | don,kal | 2021-09-24 | smash |
Tables as follows
Producer table
| ProducerId | ProducerName | DOB | Company |
|---|---|---|---|
| 2 | don | 2021-09-24 | don productions |
| 3 | kal | 2021-09-24 | kal productions |
Actor table
| Actor Id | ActorName | DOB |
|---|---|---|
| 3 | abe | 2021-09-24 |
| 4 | link | 2021-09-24 |
Movie table
| Movie Id | MovieName | ReleaseDate | Plot |
|---|---|---|---|
| 1 | hulk | 2021-09-24 | hulk smash |
Actor Movie
| Id | MovieId | ActorId |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 1 | 4 |
Producer Movie
| Id | MovieId | ActorId |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
Query I have used
Select Movie.MovieId, (Movie.MovieName),ActorName,ProducerName,ReleaseDate,Plot from Movie
inner Join ActorMovie on Movie.MovieId = ActorMovie.MovieId
inner join ProducerMovie on Movie.MovieId = ProducerMovie.MovieId
inner join Actor on ActorMovie.ActorId = Actor.ActorId
inner join Producer on ProducerMovie.ProducerId = Producer.ProducerId
