By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Quary Confusion

P: 13
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!!
May 10 '09 #1
Share this Question
Share on Google+
4 Replies


Uncle Dickie
P: 67
You should be able to concatenate the fields with something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName + ' ' + LastName
  2.  
May 11 '09 #2

P: 13
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT     TOP (100) PERCENT dbo.Movies.MovieName AS [Movie Name], dbo.Movies.Year, dbo.Movies.RunningTime AS [Running Time], 
  2.                       dbo.Actors.LastName + ', ' + dbo.Actors.FirstName AS [Actor Name], dbo.Genres.Genre, dbo.Ratings.Rating, dbo.Movies.Description
  3. FROM         dbo.Ratings INNER JOIN
  4.                       dbo.Movies INNER JOIN
  5.                       dbo.Genres ON dbo.Movies.GenreID = dbo.Genres.GenreID ON dbo.Ratings.RatingID = dbo.Movies.RatingID INNER JOIN
  6.                       dbo.MoviesActors INNER JOIN
  7.                       dbo.Actors ON dbo.MoviesActors.ActorID = dbo.Actors.ActorID ON dbo.Movies.MovieID = dbo.MoviesActors.MovieID
  8. ORDER BY 'Movie Name', [Actor Name]
Trying to use DISTINCT for the Movies table, but can't get it figured out err.
May 11 '09 #3

Uncle Dickie
P: 67
I think you would be better using a WHERE clause rather than DISTINCT to list all films that have a particular actor as:
Expand|Select|Wrap|Line Numbers
  1. Bad Boys, Smith, Will
  2. Bad Boys, Lawrence, Martin
  3. Bad Boys, Leoni, Tea
  4.  
are 3 DISTINCT entries so will still show up.

Try something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT m.MovieName
  2.        ,m.Year
  3.        ,m.RunningTime
  4.        ,g.Genre
  5.        ,r.Rating
  6.        ,m.Description
  7. FROM   dbo.Movies m
  8. JOIN   dbo.Genres g ON g.GenreID = m.GenreID
  9. JOIN   dbo.Ratings r ON r.RatingID = m.RatingID
  10. JOIN   dbo.MoviesActors ma ON ma.MovieID = m.MovieID
  11. JOIN   dbo.Actors a ON a.ActorID = ma.ActorID
  12. WHERE  a.LastName = 'Smith'
  13.   AND  a.FirstName = 'Will'
  14.  
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
May 11 '09 #4

P: 13
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.
May 11 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.