Return only one row from the right-most table for every row in the left-most table

Use:

  SELECT u.id,
         u.name,
         MIN(t.spent) AS spent
    FROM USERS u
    JOIN TRANSACTIONS t ON t.uid = u.id
GROUP BY u.id, u.name

Mind that this will only return users who have at least one TRANSACTIONS record. If you want to see users who don't have supporting records as well as those who do - use:

   SELECT u.id,
          u.name,
          COALESCE(MIN(t.spent), 0) AS spent
     FROM USERS u
LEFT JOIN TRANSACTIONS t ON t.uid = u.id
 GROUP BY u.id, u.name

If you do not care about the particular row that you get back.

select id, name, spent
from users
left join transactions on users.id = transactions.uid
group by id

This will return one row per user. It will be the first matched transaction.

Tags:

Mysql

Sql

Join