diff options
Diffstat (limited to 'DataBase/show-variants.mysql')
| -rw-r--r-- | DataBase/show-variants.mysql | 17 |
1 files changed, 7 insertions, 10 deletions
diff --git a/DataBase/show-variants.mysql b/DataBase/show-variants.mysql index 3eeeeac..8909494 100644 --- a/DataBase/show-variants.mysql +++ b/DataBase/show-variants.mysql @@ -8,16 +8,13 @@ USE lastfm * 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 ) ; +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, tmp.count - 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 ) +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 ) ) ; -DROP TABLE tmp ; - |