CODEWITHSUNDEEP
mysql
Michael St Clair
Michael St Clair

Reputation: 6625

MySQL retrieve from different table if empty

I have this query that is getting CONTRACT_PERCENTAGE from the isrc_codes table. But if TRACK_ISRC is empty I need it to get CONTRACT_PERCENTAGE from the albums table. Is there a way to do that with MySQL. On the albums table r.BUNDLE_ID = a.BUNDLE_ID and it would be a.CONTRACT_PERCENTAGE. Here is my query

$add = mysqli_query($con,"
SELECT sum(r.DISTRIBUTED_AMOUNT / r.EXCHANGE_RATE * i.CONTRACT_PERCENTAGE / 100)
FROM royalties r, isrc_codes i 
WHERE r.TRACK_ISRC = i.ISRC_CODE 
AND r.BUNDLE_ARTIST = '".$row['artistname']."' 
AND r.MONTH_ADDED = '".$current_month."'");

Upvotes: 0

Views: 29

Answers (1)

Barmar
Barmar

Reputation: 780974

Use LEFT JOIN to get NULL when there's no matching row in isrc_codes, and IFNULL to fall back to the column from albums.

$add = mysqli_query($con, "SELECT SUM(r.distributed_amount / r.exchange_rate * IFNULL(i.contract_percentage, a.contract_percentage) / 100)
                    FROM royalties r
                    LEFT JOIN isrc_codes i ON r.track_isrc = i.isrc_code
                    LEFT JOIN albums a ON r.bundle_id = a.bundle_id
                    WHERE r.bundle_artist = '{$row['artistname']}'
                    AND r.month_added = '$current_month'");

Upvotes: 2

Related Questions