At CD Baby, our "songs" database table has a list of every song on every album. (Albums are identifed by their sku we call "albumcode")
As we digitize each CD into the FLAC format, we add an "f" into the field called "encoded".
I want to find all albums where we have SOME of the FLAC files now, but not all. (The problem albums - I want to re-do these.)
Tell MySQL, "show me all albums where encoded has f AND encoded does NOT have f".
FROM songs s1
LEFT JOIN songs s2 ON s1.albumcode=s2.albumcode
WHERE s1.encoded LIKE '%f%'
AND s2.encoded NOT LIKE '%f%'
I haven’t had a query make me smile in a while. :-)
Other examples of this? Or was there a better way to solve this problem?