Newest loan per user for all users

// Edit:

All work fin开发者_开发问答e with:

SELECT u.*,
    l.cod AS loans_cod,
    l.step AS loans_step
FROM users AS u
LEFT JOIN loans AS l
ON u.id = l.users_id
WHERE l.step < 12
    OR NOT EXISTS
        (SELECT l.id
            FROM loans
            WHERE l.users_id = u.id
        )
GROUP BY u.id

Now, I can select all user, and his last loan. Thanks, and I think my solution will help future users.

if you want the last loan, you could append something like this

ORDER BY l.date DESC GROUP BY u.id

SELECT u.*,
       l.cod  AS loans_cod,
       l.step AS loans_step
FROM   users AS u
       LEFT JOIN loans AS l
         ON u.id = l.users_id
WHERE  u.id != :id  
  AND  l.timestamp = (SELECT MAX(l.timestamp)
                      FROM loans AS l2
                      WHERE l2.users_id = l.users_id)

Where timestamp is a column on your table that indicates when the loan was created or otherwise gives you a time that you can compare to.

Try this:

SELECT u.*, l.cod AS loans_cod, 
l.step AS loans_step 
FROM users AS u inner join
(
   select max(l.loan_id) as loan_id, l.users_id , l.cod, l.step
   from loans l 
   group by 
   l.users_id,l.cod,l.step
) as l
ON u.id = l.users_id  
WHERE u.id != :id 

Note: Avoid doing select * it's a very bad practice. List all the columns you need only.