masterhoo masterhoo - 6 months ago 9
MySQL Question

two tables in one SQL, query

I have two tables

ID_USER | USERNAME
1 Max
2 Jean
3 Carl
4 Sophie


and

ID_MONEY | ID_USER | MONEY
1 4 1000
2 2 1500
3 3 1250
4 1 920


I want to execute the second table SQL and order the results by username (alphabetically), in order to get this:

Carl have: 1250$
Jean have: 1500$
Max have: 920$
Sophie have: 1000$


What type of query sould I execute?
I tried,
SELECT * FROM $table_users WHERE id_user='$id_user' ORDER BY (SELECT username FROM $table_money WHERE id_user='$id_user') ASC
but not results as expected.

Answer

You need an INNER JOIN. This can be done using joins, or with the WHERE clause.

SELECT USERNAME, MONEY
FROM $table_users
JOIN $table_money USING (ID_USER)
ORDER BY USERNAME

or

SELECT USERNAME, MONEY
FROM $table_users, $table_money
WHERE $table_users.ID_USER = $table_money.ID_USER
ORDER BY USERNAME