MySQL INNER JOIN select only one row from second table

by

You need to have a subquery to get their latest date per user ID.

SELECT  a.*, c.*
FROM users a 
    INNER JOIN payments c
        ON a.id = c.user_ID
    INNER JOIN
    (
        SELECT user_ID, MAX(date) maxDate
        FROM payments
        GROUP BY user_ID
    ) b ON c.user_ID = b.user_ID AND
            c.date = b.maxDate
WHERE a.package = 1

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. What is better subquery vs literal on IN clause in mysql
  3. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  4. SQL query return data from multiple tables
  5. quick selection of a random row from a large table in mysql
  6. How to join two tables mysql?
  7. MySQL SELECT only not null values
  8. Ordering by specific field value first
  9. Mixing ANSI 1992 JOINs and COMMAs in a query
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Checking multiple columns for one value
  12. MySQL difference between two rows of a SELECT Statement
  13. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  14. MySQL INSERT INTO … VALUES and SELECT
  15. MySQL error 1241: Operand should contain 1 column(s)
  16. How to select one row randomly taking into account a weight?
  17. quick selection of a random row from a large table in mysql
  18. Compare only day and month with date field in mysql
  19. MySQL: Inner join vs Where [duplicate]
  20. Syntax error when using except in a query
  21. SELECT query return 1 row from each group
  22. MySQL Select Query – Get only first 10 characters of a value
  23. SQL query to check if a name begins and ends with a vowel
  24. How to SELECT by MAX(date)?
  25. How to find gaps in sequential numbering in mysql?
  26. limiting the rows to where the sum a column equals a certain value in MySQL
  27. Mysql select distinct
  28. How to select column names dynamically in mySQL
  29. How to select data where a field has a min value in MySQL?
  30. SQL query with avg and group by

Leave a Comment