Fill in blank data if no result found in mysql query

Question

I have a query that is checking data for 5 review sites and returning the site_id, review_count & review_average.

If there is no data for a review site then I want to return 0 for the count & average.

Is this possible to do in a mysql query?

mySQL:

SELECT rrss.review_site_id,rrss.review_count,rrss.review_average,rs.name
FROM rooftops_review_sites_snapshots rrss
LEFT JOIN review_sites rs ON rrss.review_site_id = rs.id
WHERE rrss.rooftop_id = 185
AND rrss.import_id = 16
AND rrss.review_site_id IN (31,30,12,10,29)

Current Output:

Array
(
[google] => Array
    (
        [review_site_id] => 31
        [review_count] => 24
        [review_average] => 3.80
    )

[edmunds] => Array
    (
        [review_site_id] => 12
        [review_count] => 8
        [review_average] => 4.50
    )

)

Desired Output:

Array
(
[google] => Array
    (
        [review_site_id] => 31
        [review_count] => 24
        [review_average] => 3.80
    )

[edmunds] => Array
    (
        [review_site_id] => 12
        [review_count] => 8
        [review_average] => 4.50
    )
[yelp] => Array
    (
        [review_site_id] => 31
        [review_count] => 0
        [review_average] => 0
    )

[dealerrater] => Array
    (
        [review_site_id] => 12
        [review_count] => 0
        [review_average] => 0
    )
[cars] => Array
    (
        [review_site_id] => 12
        [review_count] => 0
        [review_average] => 0
    )

)

If all the sites exist in the `review_sites` table, switch it to the predicate and use the other table in the `LEFT JOIN`. — Jared Farrish, Oct 30 '14 at 00:01

score 0 · Answer 1 · answered Oct 30 '14 at 00:10

change positions of your tables and use ISNULL(). Check query below.

SELECT rs.id,rs.name, ISNULL(rrss.review_count, 0), ISNULL(rrss.review_average, 0) FROM review_sites rs LEFT JOIN  rooftops_review_sites_snapshots rrss ON rrss.review_site_id = rs.id WHERE rrss.rooftop_id = 185 AND rrss.import_id = 16 AND rrss.review_site_id IN (31,30,12,10,29)

score 0 · Accepted Answer · answered Oct 30 '14 at 00:15

TRY THIS

SELECT 
rrss.review_site_id,
ISNULL(rrss.review_count, 0),
ISNULL(rrss.review_average, 0),
rs.name

FROM rooftops_review_sites_snapshots rrss

LEFT OUTER JOIN review_sites rs 
ON rrss.review_site_id = rs.id

WHERE rrss.rooftop_id = 185
AND rrss.import_id = 16
AND rrss.review_site_id IN (31,30,12,10,29)

Fill in blank data if no result found in mysql query

2 Answers2