Jump to content
maxxd

Limit LEFT JOIN subquery to one result

Recommended Posts

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_inventory.png.899a0bb0d5d8202a002c9807019922ea.png

product_locations

product_location.png.1ea4614c1fa0e0f8b1326be9c053f3d6.png

locations

locations.png.bc27f5f7c3ad951a1318a8f544e99f7b.png

products

products.png.40e509cc85ac620f8bfb6b174b6cf3a7.png

 

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:

results.png.69b9447c16a2104811f4cb9f35f8be04.png

 

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 by maxxd
query formatting looked like crap

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

OK, so it looks like this?

image.png.d447f24f7ab1579af3e67f605049f7af.png

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"

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.