Sean Cleveland Sean Cleveland - 2 years ago 81
MySQL Question

Display all records even if NULL

I have a view below that has everything I want to display, however it will only display if everything is added for a particular movie in every table (if that makes since). Like if movie is added, the view will only display if a copy, an actor, a role, and a genre have been connected to that particular movie, however if anything is not added for a movie, it won't display. I am new at this stuff, so any help will be greatly appreciated.

CREATE VIEW viewAllMovieDetails AS
SELECT `tblMovie`.`MovieName`, `tblCopy`.`CpyEdition`, `tblCopy`.`CpyCondition`,
`tblCopy`.`CpyAmountPaid`, `tblCopy`.`CpyNote`, `tblMovie`.`MovieRating`,
`tblMovie`.`MovieLength`,`tblMovie`.`MovieYear`, `tblActor`.`ActLastName`,
`tblActor`.`ActFirstName`, `tblRole`.`Role`, `tblMovieGenre`.`GenGenre`
FROM `tblCopy`, `tblMovie`, `tblActor`, `tblRole`, `tblMovieGenre`
WHERE `tblCopy`.`MovieID` = `tblMovie`.`MovieID` AND `tblRole`.`MovieID` = `tblMovie`.`MovieID`
AND `tblRole`.`ActID` = `tblActor`.`ActID` AND `tblMovieGenre`.`MovieID` = `tblMovie`.`MovieID`;

Answer Source

You should use LEFT JOIN. If they are any NULL's they gonna be shown with this type of join.

CREATE VIEW viewAllMovieDetails AS
SELECT tblMovie.MovieName, tblCopy.CpyEdition, tblCopy.CpyCondition,
        tblCopy.CpyAmountPaid, tblCopy.CpyNote, tblMovie.MovieRating, 
        tblMovie.MovieLength,tblMovie.MovieYear, tblActor.ActLastName, 
        tblActor.ActFirstName, tblRole.Role, tblMovieGenre.GenGenre
FROM tblCopy   
LEFT JOIN tblMovie tm USING(MovieID)
LEFT JOIN tblActor ta USING(ActID)
LEFT JOIN tblRole tr ON tr.ActID = ta.ActID
LEFT JOIN tblMovieGenre tg ON tg.MovieID = tm.MovieID;

Using aliases is good practice as well ;) If you are putting conditions in where you get inner instead of left join.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download