Code: Select all
mysql> SELECT UserID, Purchases FROM test;
+--------+-----------+
| UserID | Purchases |
+--------+-----------+
| 1 | 1 |
| 2 | 4 |
| 3 | 7 |
| 4 | 14 |
| 5 | 55 |
| 6 | 642 |
+--------+-----------+
mysql> SELECT OrdersMade, Level FROM test2;
+------------+------------------------+
| OrdersMade | Level |
+------------+------------------------+
| 0 | New Shopper |
| 5 | Eager Shopper |
| 10 | Casual Shopper |
| 15 | Regular Shopper |
| 50 | Loyal Consumer |
| 100 | College Tuition Backer |
+------------+------------------------+I can get what level one user is at by doing a query like this:
Code: Select all
mysql> SELECT UserID, Purchases, OrdersMade, Level
-> FROM test, test2
-> WHERE Purchases >= OrdersMade and UserID = 5
-> ORDER BY OrdersMade DESC;
+--------+-----------+------------+-----------------+
| UserID | Purchases | OrdersMade | Level |
+--------+-----------+------------+-----------------+
| 5 | 55 | 50 | Loyal Consumer |
| 5 | 55 | 15 | Regular Shopper |
| 5 | 55 | 10 | Casual Shopper |
| 5 | 55 | 5 | Eager Shopper |
| 5 | 55 | 0 | New Shopper |
+--------+-----------+------------+-----------------+I would like to print out a table that contains all users and their userlevels next to them so the output would look similar to this:
Code: Select all
+--------+-----------+------------------------+
| UserID | Purchases | Level |
+--------+-----------+------------------------+
| 1 | 1 | New Shopper |
| 2 | 4 | New Shopper |
| 3 | 7 | Eager Shopper |
| 4 | 14 | Casual Shopper |
| 5 | 55 | Loyal Consumer |
| 6 | 642 | College Tuition Backer |
+--------+-----------+------------------------+Code: Select all
SELECT UserID, Purchases, OrdersMade, Level
FROM test, test2
GROUP BY UserID
HAVING OrdersMade = MAX(OrdersMade)Code: Select all
mysql> SELECT UserID, Purchases, OrdersMade, Level
-> FROM test, test2
-> WHERE OrdersMade <= Purchases
-> GROUP BY UserID
-> ORDER BY UserID ASC, OrdersMade DESC;
+--------+-----------+------------+-------------+
| UserID | Purchases | OrdersMade | Level |
+--------+-----------+------------+-------------+
| 1 | 1 | 0 | New Shopper |
| 2 | 4 | 0 | New Shopper |
| 3 | 7 | 0 | New Shopper |
| 4 | 14 | 0 | New Shopper |
| 5 | 55 | 0 | New Shopper |
| 6 | 642 | 0 | New Shopper |
+--------+-----------+------------+-------------+I'm really just learning SQL so bear with me if this sounds stupid.
I'd like to know WHY it doesn't work or if there is a workaround.