Quary Confusion

[FooFighter]

I have the following tables and fields.

Actors
-ActorID
-LastName
-FirstName

Movies
-MovieID
-MovieName
-Year
.
.
.

Genres
-GenreID
-Genre

Ratings
-RatingID
-Rating

MoviesActors
-MovieActorID
-MovieID
-ActorID

I figured out how to do the joins to make it display the movie with all its actors. How would I go about making it show one field with the actors first and last name without changing the table though?

Thanks!!

[Uncle Dickie]

You should be able to concatenate the fields with something like:

SELECT FirstName + ' ' + LastName
 

[FooFighter]

Oh geesh that was easy.

Thank you very much for your assistance.

Another thing I'm trying to accomplish is this...

I've been trying to figure out how to use the DISTINCT function. Say I'm searching for all movies I have with Will Smith. If I have 3 of the main actors listed under each movie it lists them each 3 times. Here's the query I'm using to list the movies.

SELECT     TOP (100) PERCENT dbo.Movies.MovieName AS [Movie Name], dbo.Movies.Year, dbo.Movies.RunningTime AS [Running Time], 
                      dbo.Actors.LastName + ', ' + dbo.Actors.FirstName AS [Actor Name], dbo.Genres.Genre, dbo.Ratings.Rating, dbo.Movies.Description
FROM         dbo.Ratings INNER JOIN
                      dbo.Movies INNER JOIN
                      dbo.Genres ON dbo.Movies.GenreID = dbo.Genres.GenreID ON dbo.Ratings.RatingID = dbo.Movies.RatingID INNER JOIN
                      dbo.MoviesActors INNER JOIN
                      dbo.Actors ON dbo.MoviesActors.ActorID = dbo.Actors.ActorID ON dbo.Movies.MovieID = dbo.MoviesActors.MovieID
ORDER BY 'Movie Name', [Actor Name]

Trying to use DISTINCT for the Movies table, but can't get it figured out err.

[Uncle Dickie]

I think you would be better using a WHERE clause rather than DISTINCT to list all films that have a particular actor as:

Bad Boys, Smith, Will
Bad Boys, Lawrence, Martin
Bad Boys, Leoni, Tea
 

are 3 DISTINCT entries so will still show up.

Try something like:

SELECT m.MovieName
       ,m.Year
       ,m.RunningTime
       ,g.Genre
       ,r.Rating
       ,m.Description
FROM   dbo.Movies m
JOIN   dbo.Genres g ON g.GenreID = m.GenreID
JOIN   dbo.Ratings r ON r.RatingID = m.RatingID
JOIN   dbo.MoviesActors ma ON ma.MovieID = m.MovieID
JOIN   dbo.Actors a ON a.ActorID = ma.ActorID
WHERE  a.LastName = 'Smith'
  AND  a.FirstName = 'Will'
 

If you knew the ActorID rather than text, searching you query would be quicker (but I don't know how you are using your database).

I have also used table aliases in the above code as IMHO it makes the query not only quicker to write but easier to read!

Hope this helps

[FooFighter]

Actually I am going to use the ActorID as the search instead of the name. Just wanted to see where I was going wrong on the DISTINCT thing. Using WHERE does sound a lot more sensible though. Thanks for the direction. Also thanks for the info on the aliases. That does indeed looks like it'll make things look cleaner.