MySQL subquery not working

Question

Please, can you review this query? I can't retrieve the correct values with the sub-query. I believe my logic is incorrect.

I'm trying to make this work day after day, but without success.

Above the SQL query in the SQLfiddle has the comments to reproduce the error.

With this sketch you can have a better understanding of what I need.

SELECT P.nome, centroide_produto_id, similar_produto_id, 
       (SELECT preco_venda FROM precos A
        WHERE A.produto_id = CL.similar_produto_id 
          AND A.preco_id = (SELECT Max(preco_id)
                            FROM precos A2,
                                 cluster_copy3 CL2
                            WHERE A2.produto_id = A.produto_id
                              AND A2.produto_id=CL2.similar_produto_id
                              AND A2.preco_venda = (SELECT Max(preco_venda)
                                                    FROM precos A3,
                                                         cluster_copy3 CL3
                                                    WHERE A3.produto_id = CL3.similar_produto_id
                                                      AND CL3.centroide_produto_id = CL.centroide_produto_id))) AS preco_maximo
FROM produtos P,
     cluster_copy3 CL
WHERE P.produto_id = CL.centroide_produto_id 
AND CL.centroide_produto_id IN (9817, 9816)
#GROUP BY CL.centroide_produto_id

Perhaps if you explained what you want to do, there is a simpler query that would accomplish it. Simple sample data and desired results (in the question) would help. — Gordon Linoff, Aug 14 '15 at 14:09
and there seems to be some confusion about whether you want the highest price, or the most recent price — Strawberry, Aug 14 '15 at 14:15
Hi guys! Thank you for you reply! I will write some more explanation tonight, because unfortunately I'm without time right now :-/ — Ailton, Aug 14 '15 at 17:40
What I need is this: in SQLfiddle, comment the line 14 and run the query. The preco_maximo returned are: 169, 243, 149 and 100. With MAX(preco_id) and MAX(preco_venda) the correct prices I need are: 243 for centroide 9816 and 149 for centroide 9817 — Ailton, Aug 15 '15 at 01:01
Please see this [sketch](http://sencial.me/images/esquema_sql_preco.gif) — Ailton, Aug 15 '15 at 01:02
A better explanation: please see this new [sqlfiddle](http://sqlfiddle.com/#!9/ad7b2/9) with this new [sketch](http://sencial.me/images/esquema_sql_preco.gif) — Ailton, Aug 16 '15 at 03:05

Sébastien Maloron · Accepted Answer · 2015-08-17T15:08:30.053

Maybe you could try something like this, getting the data in a subquery and grouping in the main query.

    SELECT
    t.nome,
    t.centroide_produto_id,
    t.similar_produto_id,
    MAX(t.preco_maximo)
FROM
    (
        SELECT
            P.nome,
            centroide_produto_id,
            similar_produto_id,
            (
                SELECT
                    preco_venda
                FROM
                    precos A
                INNER JOIN cluster_copy3 CL2 ON A.produto_id = CL2.similar_produto_id
                WHERE
                    A.produto_id = P.produto_id
                ORDER BY
                    A.preco_id DESC,
                    A.preco_venda DESC
                LIMIT 1
            ) AS preco_maximo
        FROM
            produtos P,
            cluster_copy3 CL
        WHERE
            P.produto_id = CL.similar_produto_id
        AND CL.centroide_produto_id IN (9817, 9816)
    ) AS t
GROUP BY
    t.centroide_produto_id

MySQL subquery not working

1 Answers1