first - like inner join mysql

MySQL INNER JOIN selecciona solo una fila de la segunda tabla

Question

Tengo una tabla de users y una tabla de payments , para cada usuario, aquellos que tienen pagos, pueden tener varios pagos asociados en la tabla de payments . Me gustaría seleccionar a todos los usuarios que tienen pagos, pero solo seleccionar sus últimos pagos. Estoy probando este SQL pero nunca he intentado declaraciones SQL anidadas antes, así que quiero saber qué estoy haciendo mal. Apreciar la ayuda

        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

Answer 1

Debe tener una subconsulta para obtener su última fecha por 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

Answer 2

Hay dos problemas con su consulta:

1. Cada tabla y subconsulta necesita un nombre, por lo que debe nombrar la subconsulta INNER JOIN (SELECT ...) AS p ON ...
2. La subconsulta tal como la tiene solo devuelve un período de fila, pero realmente desea una fila para cada usuario . Para eso, necesita una consulta para obtener la fecha máxima y luego volver a unirse automáticamente para obtener toda la fila.

Suponiendo que no existen vínculos para payments.date , intente:

    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

Answer 3

La respuesta de John Woo me ayudó a resolver un problema similar. He mejorado su respuesta estableciendo el orden correcto también. Esto me ha funcionado:

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

Aunque no estoy seguro de cuán eficiente es esto.

Answer 4

Matei Mihai dio una solución simple y eficiente, pero no funcionará hasta que ponga un MAX(date) en la parte SELECT para que esta consulta se convierta en:

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

Y ordenar por no hará ninguna diferencia en la agrupación, pero puede ordenar el resultado final proporcionado por el grupo. Lo intenté y funcionó para mí.

Answer 5

Mi respuesta directamente inspirada de @valex es muy útil, si necesita varios cols en la cláusula ORDER BY.

    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

Answer 6

   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

Answer 7

SELECT u.*, p.*
FROM users AS u
INNER JOIN payments AS p ON p.user_id = u.id AND u.id = (
    SELECT id
    FROM payments AS p2
    WHERE p2.user_id = p.user_id
    ORDER BY date DESC
    LIMIT 1
)

Esta solución es mejor que la respuesta aceptada porque funciona correctamente cuando hay algunos pagos con el mismo usuario y fecha.

Answer 8

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

Echa un vistazo a este sqlfiddle