Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nmehta6
Starting Member
2 Posts |
 Posted - 2007-05-31 : 13:54:44
|
| Ok here is the situation. I have 2 tables. MoviesMovieID Title Year 21 A Beautiful Mind 2002 22 Forrest Gump 1994 23 The English Patient 1999 ActorsActorID MovieID Name 1 22 Tom Hanks 2 21 Russell Crowe 3 23 Ralph Fiennes 4 NULL Nachiket MehtaHere is the SQL Query.SELECT ActorID, Name, Title, Year FROM Actors LEFT OUTER JOIN Movies ON Actors.MovieID = Movies.MovieIDNow, I only want to show movies made in 1990's and display all 4 actors. If I putWHERE Year < 2000it won't show the fourth actor because he doesn't have any movies. I need to show all 4 actors here and NULL for movies if they don't have any.Hope this makes sense. Thank you. Nachiket |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-05-31 : 14:04:42
|
| Put your condition in the JOIN clause instead of the WHERE clause:SELECT ActorID, Name, Title, Year FROM Actors LEFT OUTER JOIN Movies ON Actors.MovieID = Movies.MovieID AND Movies.Year < 2000SQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
nmehta6
Starting Member
2 Posts |
Posted - 2007-05-31 : 14:31:18
|
 Thanks. Works.Nachiket |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
Simple Outer Join question