SQL query (LEFT JOIN + conditions) doesn't show correct results on first entry

Question

I have a problem with my SQL query. I am trying to query the database to get data from 3 different tables, where on one table conditions are applied. On the second entry it shows the results correctly, though on the first it lacks to show the results of the table where the conditions are applied.

This is my query:

SELECT
    `".PRODUCTS."`.*,
    `".CATEGORIES."`.*,
    `q_prices`.*
FROM
    `".PRODUCTS."`
LEFT JOIN
    `".CATEGORIES."`
ON
    `".PRODUCTS."`.`category_id` = `".CATEGORIES."`.`category_id`
INNER JOIN(
    SELECT
        `".PRICES."`.*
        MAX(`".PRICES."`.`price_modified`) modified
    FROM
        `".PRICES."`
    GROUP BY
        `".PRICES."`.`product_id`
    ) `q_prices`
ON
    `".PRODUCTS."`.`product_id` = `q_prices`.`product_id`

This is what it returns:

    Array
(
[0] => stdClass Object
    (
        [product_id] => 1
        [product_name] => Test product
        [product_alias] => test-product
        [category_id] => 1
        [product_created] => 2013-07-29 11:36:51
        [product_modified] => 2013-07-29 11:36:51
        [category_name] => Test categorie
        [category_alias] => test-categorie
        [category_parent] => wonenplaza.nl
        [category_created] => 2013-07-29 11:39:29
        [category_modified] => 2013-07-29 11:39:29
        [price_id] => 1
        [price_amount] => 25.00
        [price_tax] => 21
        [price_created] => 2013-07-29 11:38:18
        [price_modified] => 2013-07-29 11:38:18
        [modified] => 2013-07-29 11:38:52
    )

[1] => stdClass Object
    (
        [product_id] => 2
        [product_name] => Priva Blue ID
        [product_alias] => test-product2
        [category_id] => 1
        [product_created] => 2013-07-29 12:18:54
        [product_modified] => 2013-07-29 12:18:54
        [category_name] => Test categorie
        [category_alias] => test-categorie
        [category_parent] => wonenplaza.nl
        [category_created] => 2013-07-29 11:39:29
        [category_modified] => 2013-07-29 11:39:29
        [price_id] => 4
        [price_amount] => 20.00
        [price_tax] => 21
        [price_created] => 2013-07-29 12:19:11
        [price_modified] => 2013-07-29 12:19:11
        [modified] => 2013-07-29 13:30:05
    )
)

I think it has something to do with the limit specified on the LEFT JOIN query, but I'm not sure about that. I don't know how else to query the database to get these results.

Thanks in advance (:

May I ask what you're trying to achieve with the last join? Finding the latest price for each item? — Joachim Isaksson, Jul 29 '13 at 11:53
Yeah, I want to keep a history of the prices, but in this query I'm trying to get the latest price available for the product. — yp28, Jul 29 '13 at 11:54
there's no such thing as DESC limit. DESC is related to ORDER BY. — Roman Pekar, Jul 29 '13 at 11:56
Do you really have products without a product_ID? It looks to me that your trying to join on a field (products.product_ID) which has a null value in what your using as a Primary key. This then results in a valid join to categorie, but no such price record being found. I'm assuming the 2nd record is really what you want. So either add `where product_ID is not null` to a where clause, or fix the data so no null (perhaps it's spaces?) exists in products.product_ID — xQbert, Jul 29 '13 at 11:58
Correct, updated the first post. Though my question remains. — yp28, Jul 29 '13 at 11:58
@xQbert, in PRODUCTS.product_id it's the primary key with an auto_increment. In PRICES.product_id it's a normal integer, but all are correctly in the database with no null values. — yp28, Jul 29 '13 at 12:01
@YP28 So in your example above... why is product_ID "Blank/Null" on the first record array 0? The only way that would be possible is if there is a null value in the database, or if the copy /paste didn't work :D — xQbert, Jul 29 '13 at 12:08

Dan Bracuk · Answer 1 · 2013-07-29T12:06:24.153

0

This is how to get other data associated with most recent values.

select f1, f2, etc
from sometables
join (
select f1, max(datefield) maxdt
from table1
where whatever
group by f1
) temp on table1.datefield = maxdt
and table1.f1 = temp.f1
etc

Apply that principle to your tables and you are good to go. By the way, you want an inner join, not a left join to the derived table.

edited Jul 29 '13 at 12:06

answered Jul 29 '13 at 11:58

Dan Bracuk

20,699
4
26
43

Thanks Dan, I think this got me a little further! I updated the opening post to match what I have in my code now. It shows both results, but it shows the reverse order. I need the newest price instead of the oldest. I don't know if I understood your solution correctly, could you please review it? – yp28 Jul 29 '13 at 12:14
You only did part of it. You joined to the derived table with the product id, but not the price_modified equalling the maximum value. – Dan Bracuk Jul 29 '13 at 12:52

Joachim Isaksson · Accepted Answer · 2013-07-29T20:46:39.857

Your subquery finds the latest of all prices, no matter the product, which isn't what you need. Fixing it isn't trivial, but you could rewrite your nested query to a LEFT JOIN, which would seem to simplify things; (sorry, your tables are slightly renamed to make SQLfiddle happy)

SELECT `.PRODUCTS.`.*, `.CATEGORIES.`.*, `q_prices`.*
FROM   `.PRODUCTS.`
LEFT JOIN `.CATEGORIES.`
  ON `.PRODUCTS.`.`category_id` = `.CATEGORIES.`.`category_id`
LEFT JOIN `.PRICES.` `q_prices`
  ON `q_prices`.`product_id` = `.PRODUCTS.`.`product_id`
LEFT JOIN `.PRICES.` `dummy`
  ON `dummy`.`product_id` = `.PRODUCTS.`.`product_id`
 AND `q_prices`.`price_modified` < `dummy`.`price_modified`
WHERE `dummy`.`price_modified` IS NULL;

A simple SQLfiddle to test with.

SQL query (LEFT JOIN + conditions) doesn't show correct results on first entry

2 Answers2