[DB] show-variants to help, and remove variants removes also #2 versions

1 files changed, 18 insertions, 3 deletions

diff --git a/remove-badges-variants.mysql b/remove-badges-variants.mysql
index 69cc068..3f29d2a 100644
--- a/remove-badges-variants.mysql
+++ b/remove-badges-variants.mysql
@@ -10,8 +10,23 @@ USE lastfm
 
 DROP TABLE IF EXISTS tmp ;
 CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ;
-#SELECT badges.username, badges.type, badges.style, badges.color FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ;
-SELECT badges.png FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ;
-DELETE badges FROM badges, tmp WHERE badges.username = tmp.username AND badges.type = tmp.type AND tmp.count > 1 ;
+
+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 ;