Using MySQL, I'm looking to get the sum dollar amount of all licenses in our system, broken out or grouped by album. Our albums are stored as 6 character codes, example ABC001. Unfortunately the license tables don't store album, only the album_track, which is the album code with a number appended, example ABC001_12. So I have to do a fuzzy search for 'ABC001%'.
I'm able to run this query to get the summed license values:
FROM db1.sync_license_apps a
inner join db1.sync_license_apps_music_used b
on a.appl_ReferenceID = b.fk_appl_ReferenceID
where appl_musicUsedTrackCatNumber like 'ABC001%';
The problem is that I want to essentially run this as a subquery to my Albums table - SELECT album_code from albums. But I can't do this because I have to use a LIKE filter.
Any idea how to accomplish this? Thanks!