CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Alper Aydingül
Alper Aydingül

Reputation: 261

LEFT JOIN returns NULL if there are just one column in table

I try to get the latest created product price. Every Product is unique but can have different prices. However, my query only works if a product have more than a price as row in the product_price table:

Table product_price

This is my query:

SELECT 
    i.name AS title, 
    i.id AS product_id, 
    m.name AS manufacturer, 
    image, 
    price_sales, 
    price_new, 
    price_used, 
    price_old 
FROM product_info as i 
    LEFT JOIN product_manufacturer AS m ON i.manufacturer_id = m.id
    LEFT JOIN (SELECT * FROM product_price ORDER BY created_at DESC LIMIT 1) AS p ON i.id = p.id_product
WHERE category_id = 2 
    AND i.is_deactivated IS NULL 
LIMIT 0, 20;

I just need the latest created price row.

Result

Upvotes: 2

Views: 58

Answers (1)

GarethD
GarethD

Reputation: 69759

The problem you have is that the subquery:

(SELECT * FROM product_price ORDER BY created_at DESC LIMIT 1)

Does not get the latest price per product, but simply the latest price, so will only ever return one row, meaning only one of your products will actually have a price.

The way to resolve this is to remove any prices where a newer one exists, so for simplicity if you look just at the price table, the following will give you only the latest product prices:

SELECT  p.*
FROM    product_price AS p
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    product_price AS p2
            WHERE   p2.id_product = p.id_product
            AND     p2.created_at > p.created_at 
        );

However, MySQL will optmise LEFT JOIN/IS NULL better than NOT EXISTS (although I think the former conveys intention better), so a more efficient approach would be:

SELECT  p.*
FROM    product_price AS p
        LEFT JOIN product_price AS p2 
            ON p2.id_product = p.id_product
            AND p2.created_at > p.created_at 
WHERE   p2.id IS NULL;

Finally, introducing this back to your main query, you would end up with:

SELECT  i.name AS title, 
        i.id AS product_id, 
        m.name AS manufacturer, 
        i.image, 
        p.price_sales, 
        p.price_new, 
        p.price_used, 
        p.price_old 
FROM    product_info as i 
        LEFT JOIN product_manufacturer AS m 
            ON m.id = i.manufacturer_id
        LEFT JOIN product_price AS p 
            ON p.id_product = i.id
        LEFT JOIN product_price AS p2 
            ON p2.id_product = p.id_product
            AND p2.created_at > p.created_at 
WHERE   i.category_id = 2 
AND     i.is_deactivated IS NULL 
AND     p2.id IS NULL
LIMIT 0, 20;

Upvotes: 1

Related Questions