Mysql SELECT not working

Question

I have the tables:

users

ID | RANK | NAME | EMAIL       | PASS
01 |  1   | Foo  | foo@bar.com | $06$uhAMXXZowVIDQ.ZR1gky.u3f/UBkLW8Kd8cbyDt2eNx1qnZH2AUmW

allow

ID | RANK | STEP
01 |  1   |  1
02 |  1   |  2
03 |  1   |  3
04 |  2   |  1
05 |  4   |  *  

And, I need to know all allowed steps from user rank.

My code:

SELECT users.*, allow.step AS allow_step
  FROM users AS users LEFT JOIN allow ON users.rank = allow.rank

But only one step are selected.

Thanks for help!

Answer 1

   SELECT u.*, GROUP_CONCAT(a.step) allow_step 
     FROM users u 
LEFT JOIN allow a 
       ON u.rank = a.rank
 GROUP BY a.rank_id

This should select a list of steps separated by commas. Something like 1,2,3.

---

If you need the concatenated values to be ordered, change the first line of the query to:

   SELECT u.*, GROUP_CONCAT(a.step ORDER BY a.step) allow_step 

Answer 2

The given SQL and data will yield three rows - and would even without the use of a LEFT JOIN (a simple JOIN would suffice).

You don't show or describe how you are running the SQL; do you need to fetch multiple rows in a loop, or use some 'fetch all rows' method?