Limit LEFT JOIN subquery to one result

[maxxd]

Hi y'all. - I've been beating my forehead against this for a few hours now and though I'm sure it's something incredibly obvious and simple, I'm just not seeing the error. This is a historical inventory query - the data is as follows:

product_inventory

product_locations

locations

products

 

OK - so that's my data setup. My query is this:

SELECT	 p.productName
	,p.productCode
	,pi.current_inventory
	,pi.transaction_date
	,l.name
FROM products p
LEFT JOIN product_locations pl
  ON p.productCode = pl.product_id
LEFT JOIN (
	SELECT	 product_location_id
		,transaction_date
		,current_inventory
	FROM product_inventory
    WHERE transaction_date < '2020-01-03 23:59:59'
	ORDER BY transaction_date DESC
    LIMIT 0, 1
) pi
	ON pl.id = pi.product_location_id
LEFT JOIN locations l
  ON pl.location_id = l.id
WHERE l.id = 1;

And this is my result:

 

Note that I'm not getting a value for either current_inventory or transaction_date for product S10_1949, despite there being 3 matched rows in product_inventory. Can somebody kick me in the head to knock the dust loose and help me figure this out?

Edited January 12, 2020 by maxxd
query formatting looked like crap

[Barand]

Show me how to load those data images into a test database so I can check out the subqueries and I'll be glad to help.

[Barand]

What would you like the result to look like?. Then we know what you are trying do.

Your inventory data is only by location and date - there is no product id - so it's hard to get values for each product.

You have stock totals by product and location but I see no mention of stock in your query.

[maxxd]

product_inventory has an FK (product_location_id) to product_locations, which includes both the location ID and the product ID. product_locations.stock is used only for the stock at the actual time of the transaction - product_inventory is for historical inventory reports.

As far as loading the data, I've got seed files if that's something that'll help - I had always gotten the idea you were more straight PHP than framework, but I'd be happy to copy and paste if it'll help.

[Barand]

OK, so it looks like this?

The key to your problem lies in that join between your other tables and the subquery

Your query results
+---------------------------------------+-------------+-------------------+---------------------+-----------------+------+
| productName                           | productCode | current_inventory | transaction_date    | name            | id   |
+---------------------------------------+-------------+-------------------+---------------------+-----------------+------+
| 1969 Harley Davidson Ultimate Chopper | S10_1678    |                25 | 2020-01-03 00:00:00 | Test Location 1 |    1 |
| 1952 Alpine Renault 1300              | S10_1949    |              NULL | NULL                | Test Location 1 |    3 |
+---------------------------------------+-------------+-------------------+---------------------+-----------------+------+
                                                                                                                     |
                                                                                                                     |
            +-------------------------------------------- your join -------------------------------------------------+
            |
            |
            |                                    Subquery results
+---------------------+---------------------+-------------------+
| product_location_id | transaction_date    | current_inventory |     
+---------------------+---------------------+-------------------+
|                   1 | 2020-01-03 00:00:00 |                25 |
+---------------------+---------------------+-------------------+

Adding pl.id to your query's field list illustrates it. Your two records have values 1 and 3.

Because you LIMIT 1 in the subquery it's going to give only one record, which matches the "1"

 

[Barand]

6 hours ago, Barand said:

What would you like the result to look like?

Although you never actually confirmed your expected output, this is what I think you want

SELECT p.productName
     , p.productCode
     , pi.current_inventory
     , pi.transaction_date
     , l.name
FROM product_locations pl
    JOIN
    products p ON pl.product_id = p.productCode
    JOIN
    locations l ON pl.location_id = l.id
    JOIN 
    product_inventory pi ON pl.id = pi.product_location_id
    JOIN (
            SELECT pl.product_id
                 , MAX(transaction_date) as date
            FROM product_locations pl
                    JOIN
                 product_inventory pi ON pl.id = pi.product_location_id
            WHERE transaction_date < '2020-01-03 23:59:59'
                  AND pl.location_id = 1
            GROUP BY pl.product_id
         ) latest ON latest.product_id = pl.product_id
                  AND pi.transaction_date = latest.date;
                  
+---------------------------------------+-------------+-------------------+---------------------+-----------------+
| productName                           | productCode | current_inventory | transaction_date    | name            |
+---------------------------------------+-------------+-------------------+---------------------+-----------------+
| 1969 Harley Davidson Ultimate Chopper | S10_1678    |                25 | 2020-01-03 11:27:35 | Test Location 1 |
| 1952 Alpine Renault 1300              | S10_1949    |                11 | 2020-01-01 12:23:11 | Test Location 1 |
+---------------------------------------+-------------+-------------------+---------------------+-----------------+

Subquery finds latest date for each product and uses that date to find matching inventory record

[maxxd]

2 hours ago, Barand said:

Subquery finds latest date for each product and uses that date to find matching inventory record

That's exactly what I was looking for out of the query - sorry for the late response, my internet connection is deeply inconsistent at the moment, because ... well, because Spectrum, apparently. But thank you so much for the help - I'll dig through your SQL tomorrow morning, apply it to my full situation and data set and let you know how it turns out.

Thank you yet again, Barand!

[maxxd]

Quick update - this appears to get me the data I need - thank you @Barand!