How to get all records from multiple tables, even those with no values

Question

So I 've got many tables for a movies database (Films, FilmPersons, Persons, PersonEmails, Roles, FilmGenres, Genres).

I want to output all films that exist with as many information as possible. For example one row would be:

Title | Date | Budget | Actor | Director | Genre |  
Alien | 1923 | 3322 | someActor | someDirector | Scifi

SELECT        Films.Title, Films.Date, Films.Budget, Persons.FirstName, Persons.LastName, Roles.RoleName, Genres.Name 
FROM            Films INNER JOIN
                     FilmPersons ON Films.FilmID = FilmPersons.FilmID INNER JOIN
                     Persons ON FilmPersons.PersonID = Persons.PersonID INNER JOIN
                     Roles ON FilmPersons.RoleID = Roles.RoleID INNER JOIN
                     FilmGenres ON Films.FilmID = FilmGenres.FilmID INNER JOIN
                     Genres ON FilmGenres.GenreID = Genres.GenreID

But the above result don't show all the information about the movie, how to change the SQL so that I can select all fields?

score 2 · Answer 1 · answered Apr 08 '15 at 00:43

2

Basicly, I suppose you should use LEFT JOIN instead of INNER JOIN on such situation:

SELECT Films.Title, 
  Films.Date, 
  Films.Budget, 
  Persons.FirstName, 
  Persons.LastName, 
  Roles.RoleName, 
  Genres.Name 
FROM Films LEFT JOIN FilmPersons ON Films.FilmID = FilmPersons.FilmID 
INNER JOIN Persons ON FilmPersons.PersonID = Persons.PersonID 
INNER JOIN Roles ON FilmPersons.RoleID = Roles.RoleID 
LEFT JOIN FilmGenres ON Films.FilmID = FilmGenres.FilmID 
INNER JOIN Genres ON FilmGenres.GenreID = Genres.GenreID

While using LEFT JOIN,

FROM Films **LEFT JOIN** FilmPersons ON Films.FilmID = FilmPersons.FilmID

It would make sure that Films are the main collection, even some value of FilmPersons were missing. If you are using INNER JOIN, the final result of the query would be Intersection.

I'm not sure my explain is understandable..

answered Apr 08 '15 at 00:43

Will Hu

149
2
15

I think I understand. But in order to get all films that exist (even without any other info than their own table provides i.e. title, date and budget) I have to LEFT JOIN on everything. Does this make sense? Also, is it possible to 'merge' rows according to a specific column? i.e. one film title, but many actors or genres -> one row where actors and/or genres are separated by comma (,). Is http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-serverthis my case? – Tsafou Apr 08 '15 at 08:34
It is possible to 'merge' rows in terms of a specific column, just like two columns: one is for film name, another one is for different actors. But I'm wondering you just need to do this in SQL script? or you may display the information via web page or something? If you are going to show the film info by web pages , just let the code behind do the job using *LEFT JOIN*..It would be more better. – Will Hu Apr 10 '15 at 01:29

score 0 · Answer 2 · answered Apr 08 '15 at 01:37

Whats going to happen is for every row in films SQL Server will attempt to find matching rows in filepersons (if it does not find a matching row it will place nulls in the values that don't match) and so on. Where ever it finds a match it will put the values it finds in the matching columns otherwise it will place nulls in those columns.

SELECT Films.Title, 
  Films.Date, 
  Films.Budget, 
  Persons.FirstName, 
  Persons.LastName, 
  Roles.RoleName, 
  Genres.Name 
FROM Films LEFT JOIN FilmPersons ON Films.FilmID = FilmPersons.FilmID 
left JOIN Persons ON FilmPersons.PersonID = Persons.PersonID 
left JOIN Roles ON FilmPersons.RoleID = Roles.RoleID 
LEFT JOIN FilmGenres ON Films.FilmID = FilmGenres.FilmID 
left JOIN Genres ON FilmGenres.GenreID = Genres.GenreID

How to get all records from multiple tables, even those with no values

2 Answers2