mysql - Cómo limitar los resultados de una IZQUIERDA

subquery left-join

Question

Tomemos el caso de dos tablas: tbl_product y tbl_transaction .
tbl_product enumera los detalles del producto, incluidos los nombres y los identificadores, mientras que tbl_transaction enumera las transacciones relacionadas con los productos e incluye fechas, identificadores de producto, clientes, etc.

Necesito mostrar una página web que muestre 10 productos y para cada producto, las últimas 5 transacciones. Hasta ahora, ninguna consulta LEFT JOIN parece funcionar y la subconsulta a continuación hubiera funcionado si mysql pudiera permitir la parte tx.product_id=ta.product_id (falla con la columna Desconocida ''ta.product_id'' en ''where cláusula'': [ERROR: 1054 ] ).

SELECT  
ta.product_id,  
ta.product_name,  
tb.transaction_date  
FROM tbl_product ta  
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx WHERE tx.product_id=ta.product_id LIMIT 5) tb
LIMIT 10

¿Hay alguna forma de lograr el listado que necesito sin usar varias consultas en un bucle ?

Editar:
Esto es exactamente lo que necesito de MySQL:

SELECT ta.product_id, ta.product_name, tb.transaction_date ...  
FROM tbl_product ta  
LEFT JOIN tbl_transaction tb ON (tb.product_id=ta.product_id LIMIT 5)  
LIMIT 10

Por supuesto, esto es ilegal, ¡pero realmente deseo que no lo fuera!

Answer 1

Aquí es donde las funciones de clasificación serían muy útiles. Desafortunadamente, MySQL todavía no los soporta. En su lugar, puede intentar algo como lo siguiente.

Select ta.product_id, ta.product_name
    , tb.transaction_date
From tbl_product As ta
    Left Join   (
                Select tx1.product_id, tx1.transaction_id, tx1.transaction_date
                    , (Select Count(*)
                        From tbl_transaction As tx2
                        Where tx2.product_id = tx1.product_id
                            And tx2.transaction_id < tx1.transaction_id) As [Rank]
                From tbl_transaction As tx1
                ) as tb
        On tb.product_id = ta.product_id
            And tb.[rank] <= 4
Limit 10

Answer 2

Editar:

Si desea imponer un límite en cada producto, lo separaría en dos consultas.

Primero obtenga una lista de 10 productos y luego ejecute otra consulta para cada uno de los que devuelven las últimas cinco transacciones.

Answer 3

Encontré la forma de LEFT JOIN con subconsulta, ordenada y limitada, funciona en RDS Amazon, MariaDB v10.2

LEFT JOIN activities last_activity ON last_activity.id = ( 
     SELECT id FROM activities 
     WHERE contester_id = contesters.id AND `type` IN ({$last_activity_types}) 
     ORDER BY id DESC LIMIT 1 
)

Todas las otras formas de hacer LEFT JOIN con GROUPING y SORTING no funcionaron para mí. Este ejemplo es excepción de trabajo.

Answer 4

Falla porque cuando colocas paréntesis alrededor de tu consulta y le das el alias "tb" has creado una tabla derivada. Su tabla derivada no tiene conocimiento de la tabla tbl_product que tiene el alias "ta"

Intente usar ON o WHERE fuera de la tabla derivada, luego haga referencia a esa tabla usando el alias "tb" que proporcionó

EDITAR:

El uso de "LIMIT" limita los resultados de la consulta en su totalidad. Si bien tiene "LÍMITE 10", lo que realmente desea es 50 filas (o menos si hay menos de 5 históricos), ¿es así?

10 productos, unidos a 5 registros históricos, devolviendo 50 filas totales.

En este caso, puede hacer que una única solución de consulta se una a la tabla del producto en una tabla derivada que tenga "LÍMITE 10"

Como:

SELECT *
FROM tbl_product ta
JOIN (SELECT * FROM tbl_product tz WHERE tz.product_id = ta.product_id LIMIT 10) tc
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx 
    WHERE tx.product_id=ta.product_id LIMIT 5) tb

También puede usar "in" y especificar los 10 principales, como:

SELECT *
FROM tbl_product ta
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx 
WHERE tx.product_id=ta.product_id LIMIT 5) tb 
WHERE ta.product_id IN 
  (SELECT z.product_id FROM tbl_product z LIMIT 10)