CODEWITHSUNDEEP
mysqlleft-join
el_pup_le
el_pup_le

Reputation: 12189

Left join resulting in null rows for column with no null rows

This has me confused, I'm getting NULL columns for this query even though the table movie_image doesn't have any NULL values in the column mi.filename.

SELECT m.*, mi.filename, COUNT(p.movieid) AS publicationsCount
                FROM movies m
           LEFT JOIN (movie_publications p, movie_image mi)
                  ON (m.movieid = p.movieid
                      AND
                      p.movieid = mi.movieid)
               GROUP BY m.movieid

Upvotes: 1

Views: 347

Answers (3)

stivlo
stivlo

Reputation: 85546

A left Join will take all rows from the first table (movies) and try to match with the second table (movie_publications). When it's not possible NULL columns will be inserted instead of the columns of the second table.

If you don't want this to happen you should use an INNER JOIN.

UPDATE: you said in the comments that movies can have or not publication, but will always have an image, so you could rewrite the query as follows. In case you're not totally sure that all movies have an image you could use LEFT JOIN also for movie_image.

SELECT m.*, mi.filename, COUNT(p.movieid) AS publicationsCount
    FROM movies m
    LEFT JOIN movie_publications p
        ON (m.movieid = p.movieid)
    INNER JOIN movie_image mi
        ON (m.movieid = mi.movieid)
    GROUP BY m.movieid

Upvotes: 4

glglgl
glglgl

Reputation: 91139

If I get you right, it is exactly what LEFT JOIN is for: to get a result even for "non-matching joins".

Your query walks through movies and emits at least one result row for every row, even if there is no "matching partner" in the other tables.

Upvotes: 1

xdazz
xdazz

Reputation: 160933

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table, and NULL is for the no matches.

Upvotes: 1

Related Questions