MySQL JOIN query help: return only one row from the right-most table for every row in the left-most table

Question

I have two tables. I want to join them in a way that only one record in the right table is returned for each record in the left most table. I've included an example below. I'd like to avoid subqueries and temporary tables as the actual data is about 4M rows. I also don't care which record in the rightmost table is matched, as long as one or none is matched. Thanks!

table users:

-------------
| id | name |
-------------
| 1  | mike |
| 2  | john |
| 3  | bill |
-------------

table transactions:

---------------
| uid | spent | 
---------------
| 1   | 5.00  |
| 1   | 5.00  |
| 2   | 5.00  |
| 3   | 5.00  |
| 3   | 10.00 |
---------------

expected output:

---------------------
| id | name | spent |
---------------------
| 1  | mike | 5.00  |
| 2  | john | 5.00  |
| 3  | bill | 5.00  |
---------------------

Answer 1

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

Answer 2

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.

Cheers, Jacob

Answer 3

My apologies if this doesn't actually answer your question. It looks like you are trying to see which users have at least one transaction. You could do this and in the process see how much each user has spent by doing something like this:

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

Answer 4

See SO 3305709 for an equivalent recent question, with a number of reasonable answers. The DBMS cited there is Postgres, but the differences here are negligible.