SQL joining 3 tables when 1 table is emty

Question

I am trying to write a query that connects 3 tables.

The first table is info about each festival. The second table is the number of votes for each festival. The third table is reviews for each festival.

I want to join all 3 tables so I get all the columns from table1, join table1 with table2 on the festivalid, but I also need to count the number of records in table 3 that apply to each festival.

The first 2 tables give me a result because they both have data in them but table 3 is empty because there are no reviews yet so adding that to my query gives me no results.

SELECT  f.*,
        v.total,
        v.votes,
        v.festivalid,
        r.reviewcount as count
    FROM festivals f
INNER
    JOIN vote v
        ON f.festivalid = v.festivalid
INNER 
    JOIN (SELECT festivalid,
                 count(*) as reviewcount
            FROM reviews)
            GROUP BY festivalid) as r

        on r.festivalid = v.festivalid

Answer 1

LEFT OUTER JOIN

e.g.

SELECT  f.*, 
        v.total, 
        v.votes, 
        v.festivalid, 
        r.reviewcount as count 
    FROM festivals f 
INNER 
    JOIN vote v 
        ON f.festivalid = v.festivalid 
LEFT OUTER
    JOIN (SELECT festivalid, 
                 count(*) as reviewcount 
            FROM reviews 
            GROUP BY festivalid) as r 

        on r.festivalid = v.festivalid 

Answer 2

The second join shouldn't be an inner join.

SELECT  f.*,
            v.total,
            v.votes,
            v.festivalid,
            r.reviewcount as count
        FROM festivals f
    INNER
        JOIN vote v
            ON f.festivalid = v.festivalid
    LEFT OUTER
        JOIN (SELECT festivalid,
                     count(*) as reviewcount
                FROM reviews
                GROUP BY festivalid) as r

            on r.festivalid = v.festivalid

Article on outer joins: http://msdn.microsoft.com/en-us/library/ms187518.aspx

Answer 3

Try using a outer join on your review count table (r)

SELECT  f.*,
        v.total,
        v.votes,
        v.festivalid,
        r.reviewcount as count
    FROM festivals f
INNER
    JOIN vote v
        ON f.festivalid = v.festivalid
LEFT OUTER
    JOIN (SELECT festivalid,
                 count(*) as reviewcount
            FROM reviews)
            GROUP BY festivalid) as r
        on r.festivalid = v.festivalid

Answer 4

SELECT  f.*,
        v.total,
        v.votes,
        v.festivalid,
        ifnull(r.reviewcount,0) as count
    FROM festivals f
INNER
    JOIN vote v
        ON f.festivalid = v.festivalid
LEFT OUTER 
    JOIN (SELECT festivalid,
                 count(*) as reviewcount
            FROM reviews)
            GROUP BY festivalid) as r

        on r.festivalid = v.festivalid

added ifnull to show reviews as 0 in case there are none.