Doubt about subquery

Question

I have several images for a site that I am doing. I wanted to show only the last image that was registered for a particular product. I know with left Join will pull up all the images and that I need a subquery to pull this last image, but this query is not working.

Thanks in advance

<?php
$query = "SELECT * FROM {$this->prefix}produtos p ";
$query .= " left join {$this->prefix}imagens img on img.img_pro_id = pro_id ";
$query .= " INNER JOIN {$this->prefix}categorias c ON p.pro_categoria = c.cate_id ";
$query .= " WHERE pro_ativo = 1 ORDER BY pro_id DESC";

Answer 1

Have you tried using OUTER APPLY instead of LEFT JOIN?

For example, if you have a table of images by product ID where you have some type of index to sort by the latest (for example, image date), you can use a query similar to this one below to select the newest item.

SELECT *
FROM {$this->prefix}produtos P
OUTER APPLY(
    SELECT TOP 1
    [CAMINHO_IMAGEM]
    FROM {$this->prefix}imagens AX
    WHERE AX.pro_id = P.pro_id
    ORDER BY [ALGUM_INDICE] DESC
) IMG