USE lastfm


/**
 * Deletes all entries in badges
 * for each unique username + type
 * where more than one style or color exists
 */

CREATE TEMPORARY TABLE variants (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;

SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, variants.count
	FROM badges, variants
	WHERE badges.username = variants.username
	AND ( (badges.type = variants.type AND variants.count > 1)
	   OR ( (badges.type LIKE concat(variants.type,"%") OR variants.type LIKE concat(badges.type,"%"))
		 AND badges.type != variants.type )
	   ) ;