mysql sub query reference outer table

Question

I am trying to use the outer table in a sub query select. While I can hardcode the value and get the required results; I really need to use the outer phppos_items table; but cannot figure it out. Below is the simplified query. Is there any way to do this? The reason I am using a sub query as I want to get only one row in the join.

The part I am trying to get the outer table is XXX

SELECT SUM(trans_current_quantity) as quantity FROM `phppos_items` 
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and `phppos_location_items`.`location_id` IN(1)
LEFT JOIN 
(SELECT * FROM phppos_inventory WHERE trans_date < '2018-05-06 23:59:59' and
trans_items = *XXX* ORDER BY trans_date DESC LIMIT 1) as inventory 
ON `phppos_location_items`.`item_id` = `inventory`.`trans_items`

Gordon Linoff · Accepted Answer · 2018-05-08 02:14:27Z

2

This is your query, with table aliases making it a bit more readable:

SELECT SUM(trans_current_quantity) as quantity
FROM phppos_items i LEFT JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) LEFT JOIN 
     (SELECT * 
      FROM phppos_inventory inv
      WHERE inv.trans_date < '2018-05-06 23:59:59' and
            trans_items = *XXX*
      ORDER BY trans_date DESC LIMIT 1
     ) inv 
    ON li.item_id = inv.trans_items;

I can only interpret trans_current_quantity as coming from phppos_inventory. The LEFT JOINs appear to be superfluous as you've written the query.

What you really want is a lateral join. That doesn't work in MySQL, alas. Here is the next closest thing:

SELECT SUM(inv.trans_current_quantity) as quantity
FROM phppos_items i JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) JOIN 
     phppos_inventory inv
     ON li.item_id = inv.trans_items
WHERE li.item_id = XXX AND
      inv.trans_date = (SELECT MAX(inv2.trans_date)
                        FROM phppos_inventory inv2
                        WHERE inv2.trans_date < '2018-05-07' and
                              inv2.trans_items = li.item_id
                       );

I also fixed the date comparison by adding a second. If that's not right, you can fix it, but the query is more readable.

edited May 8, 2018 at 2:14

answered May 7, 2018 at 19:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Muench Over a year ago

Do you mean WHERE li.item_id = i.item_id?

Gordon Linoff Over a year ago

@ChrisMuench . . . The references in the subquery should be to inv2.

Morcous Wahba · Accepted Answer · 2018-05-07 19:31:17Z

-1

Assuming you want to replace XXX with the primary key to phppos_items (i.e. item_id), you probably just need to alias the table in the outer query and reference it by the alias in the subquery. If trans_items references something other than the Primary Key (or if item_id) is not the Primary Key, then I'll need more information to help...

SELECT SUM(trans_current_quantity) as quantity FROM `phppos_items` AS items1
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and `phppos_location_items`.`location_id` IN(1)
LEFT JOIN 
    (SELECT * FROM phppos_inventory WHERE trans_date < '2018-05-06 23:59:59' and trans_items = items1.item_id ORDER BY trans_date DESC LIMIT 1) as inventory 
     ON `phppos_location_items`.`item_id` = `inventory`.`trans_items`

answered May 7, 2018 at 19:31

Morcous Wahba

493 bronze badges

1 Comment

Chris Muench Over a year ago

I get error #1054 - Unknown column 'items1.item_id' in 'where clause'

Collectives™ on Stack Overflow

mysql sub query reference outer table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related