I have 5 tables ( movies – categories - movie-catecories - directors-Movie_Directors)
title varchar (50)
image varchar(100)
categories
cat_Id int
cat_name varchar(50)
movie-catecories
MovieCatrgory_ID int
cat_Id int
movie_Id int
directors
director_Id int
Name varchar(50)
Movie_Directors
MovieDirectors_ID int
movie_Id int
director_Id int
DirectorName varchar(50)
| movie_Id | title | image |
| 1 | Hyde Park on Hudson | movies/1.jpg |
| 2 | Playing for Keeps | movies/2.jpg |
| 3 | Cheerful Weather for the Wedding | movies/3.jpg |
| 4 | Heleno | movies/4.jpg |
| 8 | Deadfall | movies/6.jpg |
categories
| Cat_Id | Cat-name |
| 1 | Drama |
| 2 | Romance |
| 3 | Comedy |
| 4 | Romance |
| 5 | Crime |
movie-catecories
| MovieCatrgory_ID | Cat_Id | movie_Id |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
| 5 | 3 | 8 |
Directors
| Director_ID | Name |
| 1 | Roger Michell |
| 2 | Gabriele Muccino |
| 3 | Donald Rice |
| 4 | José Henrique Fonseca |
| 5 | Sung-Hee Jo |
Movie_Directors
| MovieDirectors_ID | movie_Id | director_Id | DirectorName |
| 1 | 1 | 1 | Roger Michell |
| 2 | 1 | 2 | Gabriele Muccino |
| 4 | 1 | 3 | Donald Rice |
| 5 | 2 | 4 | José Henrique Fonseca |
| 6 | 2 | 5 | Sung-Hee Jo |
Sql:
SELECT DISTINCT movies.movie_Id, movies.title, movies.image, Movie_Directors.DirectorName, Movie_Directors.director_Id
FROM movies INNER JOIN
Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN
directors
WHERE (movies.title LIKE '%' + @title + '%')
Result
| Movie_ID | tittle | image | DirectorName | director_Id |
| 1 | Hyde Park on Hudson | movies/1.jpg | Roger Michell | 1 |
| 1 | Hyde Park on Hudson | movies/1.jpg | Gabriele Muccino | 2 |
| 1 | Hyde Park on Hudson | movies/1.jpg | Donald Rice | 3 |
I want to combine the DirectorName in one record like this
| Movie_ID | tittle | image | DirectorName | director_Id |
| 1 | Hyde Park on Hudson | movies/1.jpg | Roger Michell, Gabriele Muccino, Donald Rice | 1-2-3 |
I have problem in this code to combine the DirectorName in one record
Please help me
SELECT DISTINCT movies.movie_Id, movies.title, movies.image,
substring(
(select '; ' +Movie_Directors . DirectorName
from Movie_Directors
inner join movies
on Movie_Directors.movie_Id = movies.movie_Id
for xml path(")), 3, 1000) as CombineDirectorName
FROM movies INNER JOIN
Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN
directors
WHERE (movies.title LIKE '%' + @title + '%')
and display the result in Repeater
<asp:Repeater ID="Repeater1" runat="server" >
<HeaderTemplate> </HeaderTemplate>
<ItemTemplate>
<div style="width:100%;">
<div class="excerpt">
<a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="thumb" title="An image"><img src="<%# DataBinder.Eval(Container.DataItem, "image")%>" alt="Post" style="opacity: 1; float:left; width:80px ; height:100px; border:3px solid #fff ; margin:5px;"></a>
<a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="header"><h6><%# DataBinder.Eval(Container.DataItem, "title")%>
</h6></a>
<div style="padding:5px;"><%# DataBinder.Eval(Container.DataItem, " Movie_Directors . DirectorName ")%></div>
</div>
</div><br />
<hr />
</ItemTemplate>
</asp:Repeater>
C#
protected void LinkButton1_Click(object sender, EventArgs e)
{
string cat_id = DropDownList1.SelectedValue;
string keyword = TextBox1.Text;
int? cid = int.Parse(cat_id);
Repeater1.DataSource = dc.StoredProcedure1(TextBox1.Text, TextBox1.Text, cid);
Repeater1.DataBind();
}