I have 5 tables ( movies – categories - movie-catecories - directors-Movie_Directors)
movies
movie_Id int
title varchar (50)
image varchar(100)
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)
movies
| 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-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 |
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 + '%')
| tittle | image | DirectorName | director_Id | |
| 1 | Hyde Park on Hudson | movies/1.jpg | Roger Michell | 1 |
| Hyde Park on Hudson | movies/1.jpg | 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();
}
Fadi alfadiPosted Mar 1, 2015, 5:03 AM
like: search by title and or actor and or DirectorName
title
SELECT movies.movie_Id, movies.title, movies.image, Movie_Directors.DirectorName
FROM movies INNER JOIN
Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS
WHERE (movies.title = "" or movies.title LIKE '%' + @title + '%') and (movies.actor= "" or movies.actor LIKE '%' + @actor+ '%')and (Movie_Directors.DirectorName= "" or Movie_Directors.DirectorName LIKE '%' + @DirectorName+ '%')
Group by DirectorName
c#:
protected void LinkButton1_Click(object sender, EventArgs e)
{
string title = TextBox1.Text;
string actor = TextBox2.Text;
string DirectorName = TextBox3.Text;
Repeater1.DataSource = dc.StoredProcedure1(TextBox1.Text, TextBox2.Text, TextBox3.Text);
Repeater1.DataBind();
GridView1.DataBind();
}
please help me
Manish Kumar ChoudharyPosted Feb 9, 2015, 6:58 AM
Fadi alfadiPosted Feb 9, 2015, 6:57 AM
Manish Kumar ChoudharyPosted Feb 8, 2015, 10:53 PM
Fadi alfadiPosted Feb 8, 2015, 4:58 AM
CREATE PROCEDURE dbo.StoredProcedurcombine
@title nvarchar(50)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
WITH Directorlist (movie_Id,title,DirectorName ) as
(
select Movie_Directors.movie_Id,title ,DirectorName from dbo.Movie_Directors
left outer join directors
on directors.director_Id =Movie_Directors.director_Id
left outer join
movies
on Movie_Directors.movie_Id =movies.movie_Id
),
ListForDirectorName as(
SELECT movie_Id ,title, LEFT(DirectorList , LEN(DirectorList)-1) AS DirectorNameList
FROM Directorlist AS extern
CROSS APPLY
(
SELECT DirectorName + ' , '
FROM Directorlist AS intern
WHERE extern.movie_Id = intern.movie_Id
FOR XML PATH('')
)pre_trimmed (Directorlist)
Group by movie_Id,Directorlist,title
)
SELECT * FROM ListForDirectorName where (movies.title LIKE '%' + @title + '%')
Fadi alfadiPosted Feb 8, 2015, 3:03 AM
Fadi alfadiPosted Feb 5, 2015, 7:50 AM
Manish Kumar ChoudharyPosted Feb 5, 2015, 7:47 AM
http://www.c-sharpcorner.com/UploadFile/ee01e6/how-to-get-comma-separated-values-in-sql-server-with-group/
Manish Kumar ChoudharyPosted Feb 5, 2015, 7:42 AM
MoviesList as(
SELECT movie_Id ,title,image,DirectorName,
LEFT(director_Id , LEN(CAST(director_Id as varchar(5))-1) AS DirectorList
FROM MoviesTempList AS extern
CROSS APPLY
(
SELECT CAST(director_Id as varchar(5))+ ' - '
FROM MoviesTempList AS intern
WHERE extern.director_Id = intern.director_Id
FOR XML PATH('')
)pre_trimmed (director_Id)
Group by movie_Id ,title,image,DirectorName,DirectorList
)
Manish Kumar ChoudharyPosted Feb 5, 2015, 7:38 AM
show me what error you are getting ?
Fadi alfadiPosted Feb 5, 2015, 7:35 AM
Manish Kumar ChoudharyPosted Feb 5, 2015, 7:27 AM
WITH MoviesTempList as
(
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 + '%')
),
MoviesList as(
SELECT movie_Id ,title,image,DirectorName,
LEFT(director_Id , LEN(director_Id)-1) AS DirectorList
FROM MoviesTempList AS extern
CROSS APPLY
(
SELECT CAST(director_Id as varchar(5))+ ' - '
FROM MoviesTempList AS intern
WHERE extern.director_Id = intern.director_Id
FOR XML PATH('')
)pre_trimmed (director_Id)
Group by movie_Id ,title,image,DirectorName,DirectorList
)
select * from MoviesList