1 files changed, 32 insertions, 0 deletions
diff --git a/DataBase/remove-badges-variants.mysql b/DataBase/remove-badges-variants.mysql
new file mode 100644
index 0000000..3f29d2a
--- /dev/null
+++ b/DataBase/remove-badges-variants.mysql
@@ -0,0 +1,32 @@
+
+USE lastfm
+
+
+/**
+ * Deletes all entries in badges
+ * for each unique username + type
+ * where more than one style or color exists
+ */
+
+DROP TABLE IF EXISTS tmp ;
+CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
+
+SELECT badges.png
+	FROM badges, tmp
+	WHERE badges.username = tmp.username
+	AND ( (badges.type = tmp.type AND tmp.count > 1)
+	   OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%"))
+		 AND badges.type != tmp.type )
+	   ) ;
+
+DELETE badges.*
+	FROM badges, tmp
+	WHERE badges.username = tmp.username
+	AND ( (badges.type = tmp.type AND tmp.count > 1)
+	   OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%"))
+		 AND badges.type != tmp.type )
+	   ) ;
+
+
+DROP TABLE tmp ;
+