MySQL INNER JOIN select only one row from second table

Question

I have a users table and a payments table  for each user  those of which have payments  may have multiple associated payments in the payments table  I would like to select all users who have payments  but only select their latest payment  I m trying this SQL but i ve never tried nested SQL statements before so I want to know what i m doing wrong  Appreciate the help  SELECT u    FROM users AS u     INNER JOIN           SELECT p           FROM payments AS p         ORDER BY date DESC         LIMIT 1           ON p user id   u id WHERE u package   1

User · Answer

There are two problems with your query    Every table and subquery needs a name  so you have to name the subquery INNER JOIN  SELECT      AS p ON       The subquery as you have it only returns one row period  but you actually want one row for each user  For that you need one query to get the max date and then self-join back to get the whole row    Assuming there are no ties for payments date  try       SELECT u    p        FROM           SELECT MAX p date  AS date  p user id          FROM payments AS p         GROUP BY p user id       AS latestP     INNER JOIN users AS u ON latestP user id   u id     INNER JOIN payments AS p ON p user id   u id AND p date   latestP date     WHERE u package   1

User · Answer

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

User · Answer

My answer directly inspired from  valex very usefull  if you need several cols in the ORDER BY clause        SELECT u        FROM users AS u     INNER JOIN           SELECT p              num    if  id   user id   num   1  1  as row number            id    user id as tmp         FROM  SELECT   FROM payments ORDER BY p user id ASC  date DESC  AS p                SELECT  num    0  x                SELECT  id    0  y               ON  p user id   u id  and  p row number 1      WHERE u package   1

User · Answer

SELECT u    p   FROM users AS u INNER JOIN payments AS p ON p id         SELECT id     FROM payments AS p2     WHERE p2 user id   u id     ORDER BY date DESC     LIMIT 1    Or SELECT u    p   FROM users AS u INNER JOIN payments AS p ON p user id   u id WHERE NOT EXISTS       SELECT 1     FROM payments AS p2     WHERE         p2 user id   p user id AND          p2 date  gt  p date OR  p2 date   p date AND p2 id  gt  p id      This solutions are better than the accepted answer because they work correctly when there are multiple payments with same user and date  You can try on SQL Fiddle

User · Answer

SELECT U    V   FROM users AS U  INNER JOIN  SELECT   FROM payments WHERE id IN   SELECT MAX id  FROM payments GROUP BY user id    AS V ON U id   V user id   This will get it working

User · Answer

You can try this   SELECT u    p   FROM users AS u LEFT JOIN       SELECT    ROW NUMBER   OVER PARTITION BY userid ORDER BY  Date  DESC  AS RowNo     FROM payments     AS p ON u userid   p userid AND p RowNo 1

User · Answer

SELECT u    p    max p date  FROM payments p JOIN users u ON u id p user id AND u package   1 GROUP BY u id ORDER BY p date DESC   Check out this sqlfiddle

User · Answer

Matei Mihai given a simple and efficient solution but it will not work until put a MAX date  in SELECT part so this query will become   SELECT u    p    max date  FROM payments p JOIN users u ON u id p user id AND u package   1 GROUP BY u id   And order by will not make any difference in grouping but it can order the final result provided by group by  I tried it and it worked for me

User · Answer

This is quite simple do The inner join and then group by user id and use max aggregate function in payment id assuming your table being user and payment query can be select user id  max payment id  from user inner join payment on  user id   payment user id  group by user id

User · Answer

John Woo s answer helped me solve a similar problem  I ve improved upon his answer by setting the correct ordering as well  This has worked for me   SELECT  a    c   FROM users a      INNER JOIN payments c         ON a id   c user ID     INNER JOIN           SELECT user ID  MAX date  as maxDate FROM                       SELECT user ID  date             FROM payments             ORDER BY date DESC           d         GROUP BY user ID       b ON c user ID   b user ID AND            c date   b maxDate WHERE a package   1   I m not sure how efficient this is  though

User · Answer

SELECT u            FROM users AS u         INNER JOIN               SELECT p                  num    if  id   user id   num   1  1  as row number                id    user id as tmp             FROM payments AS p                    SELECT  num    0  x                    SELECT  id    0  y             ORDER BY p user id ASC  date DESC          ON  p user id   u id  and  p row number 1          WHERE u package   1

[mysql] MySQL INNER JOIN select only one row from second table

Examples related to mysql

Examples related to sql

Examples related to select

Examples related to inner-join