Results 1 to 19 of 19

Thread: Mysql Nested sub query

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    89

    Mysql Nested sub query

    Dear All

    I am trying to run a query that returns inventory items and the last purchase date, price and order number used to make the purchase
    I am guessing that a sub query would do this but am struggling to get this to work.

    Here are my tables:
    CREATE TABLE `inventory` (
    `item_code` char(5) NOT NULL,
    `item_description` varchar(20) default NULL,
    `item_unit` varchar(5) default 'Ea',
    PRIMARY KEY (`item_code`),
    UNIQUE KEY `item_code` (`item_code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE `inventory_purchases` (
    `purchase_id` int(11) NOT NULL auto_increment,
    `item_code` char(5) default NULL,
    `order_number` varchar(5) default NULL,
    `order_date` date default NULL,
    `price` decimal(8,2) default NULL,
    PRIMARY KEY (`purchase_id`),
    UNIQUE KEY `purchase_id` (`purchase_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

    INSERT INTO `inventory` (`item_code`, `item_description`, `item_unit`) VALUES
    ('ABC01','Item Number One','Ea'),
    ('ABC02','Item Number Two','Ea'),
    ('ABC03','Item Number Three','Ea');
    COMMIT;

    INSERT INTO `inventory_purchases` (`purchase_id`, `item_code`, `order_number`, `order_date`, `price`) VALUES
    (1,'ABC01','1234','2022-07-01',10.54),
    (2,'ABC01','1235','2022-07-06',10.84),
    (3,'ABC03','1236','2022-07-07',35.42),
    (4,'ABC02','1237','2022-07-07',22.36),
    (5,'ABC01','1238','2022-07-09',10.33),
    (6,'ABC02','1239','2022-07-11',21.58);
    COMMIT;


    I need a query that returns the following:

    item_code |item_description| item_unit|order_number|order_date|price
    ABC01|Item Number One |Ea |1238 |2022-07-09| 10.33
    ABC02|Item Number Two |Ea |1239 |2022-07-11| 21.58
    ABC03|Item Number Three |Ea |1236 |2022-07-07| 35.42

    any assistance would be greatly appreciated

    Regards

    Ken

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Tested in SQLite (No MySQL at Hand).
    ROW_NUMBER might have a different Syntax on MySQL, but researching it couldn't find any difference
    sql Code:
    1. SELECT
    2.     i.item_code,
    3.     i.item_description,
    4.     i.item_unit,
    5.     ip.order_number,
    6.     ip.order_date,
    7.     ip.price
    8. FROM inventory As i
    9. LEFT JOIN
    10.     (SELECT
    11.         item_code,
    12.         order_number,
    13.         order_date,
    14.         price,
    15.         ROW_NUMBER() OVER(PARTITION BY item_code ORDER BY order_number DESC, order_date DESC) As RN
    16.     FROM inventory_purchases) As ip
    17. ON i.item_code=ip.item_code and ip.rn=1
    18. ORDER BY i.item_code
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Returns:

    ABC01 Item Number One Ea 1238 2022-07-09 10.33
    ABC02 Item Number Two Ea 1239 2022-07-11 21.58
    ABC03 Item Number Three Ea 1236 2022-07-07 35.42


    Notes: If order_number is unique (which it should be) you can leave out the second order by-argument of order_date within the ROW_NUMBER.
    OTOH, if you need the last order, first going by date, then by order_number, you'd need both (and swap the arguments: Date first, number second)
    since you can have more than one order on the same day, but one order can only have one date

    EDIT: Oh, and item_code in inventory_purchases should be Foreign Key to item_code in inventory, including ON UPDATE CASCADE,
    except it's intentional

    EDIT2: Important in this case: the ".... ip.RN=1" must stay in the ON-Clause.
    If you move it to the WHERE-Clause of the "outer" SELECT you won't see items from "inventory" which don't have any purchase so far
    Last edited by Zvoni; Aug 8th, 2022 at 06:27 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    89

    Re: Mysql Nested sub query

    Hi Zvoni

    Thank you so much, when i run the query i get the following error:
    'VIEW'S SELECT CONTAINS A SUB QUERY IN THE FROM CLAUSE'

    Any Ideas?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    89

    Re: Mysql Nested sub query

    I am using Mysql 5.0

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Quote Originally Posted by KenBZim2 View Post
    I am using Mysql 5.0
    OUCH! Why such an old version?

    Quote Originally Posted by KenBZim2 View Post
    Hi Zvoni

    Thank you so much, when i run the query i get the following error:
    'VIEW'S SELECT CONTAINS A SUB QUERY IN THE FROM CLAUSE'

    Any Ideas?
    https://dev.mysql.com/doc/refman/5.6...eate-view.html
    A view definition is subject to the following restrictions:

    • The SELECT statement cannot contain a subquery in the FROM clause.

    If i understand it correctly, with MySQL5.7 and newer you don't have that restriction, MySQL5.6 and older have that restriction

    Workaround: Create a separate View for the Inner SELECT (The one inside the LEFT JOIN).
    Note: You'll probably pay performance penalties for the (multiple) views

    First view ("helper-view"):
    sql Code:
    1. CREATE VIEW "vw_ip" AS
    2. SELECT
    3.         item_code,
    4.         order_number,
    5.         order_date,
    6.         price,
    7.         ROW_NUMBER() OVER(PARTITION BY item_code ORDER BY order_number DESC, order_date DESC) As RN
    8.     FROM inventory_purchases

    Second View (which would be your "main" view)
    sql Code:
    1. CREATE VIEW "vw_inventory_purchases" AS
    2. SELECT
    3.     i.item_code,
    4.     i.item_description,
    5.     i.item_unit,
    6.     ip.order_number,
    7.     ip.order_date,
    8.     ip.price
    9. FROM inventory As i
    10. LEFT JOIN vw_ip As ip
    11. ON i.item_code=ip.item_code and ip.rn=1
    12. ORDER BY i.item_code

    See also: https://stackoverflow.com/questions/...he-from-clause
    Last edited by Zvoni; Aug 8th, 2022 at 07:27 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,321

    Re: Mysql Nested sub query

    Try this query without a window function (the ROW_NUMBER bit)

    https://www.db-fiddle.com/f/tZJbvdrRUNUrx8g18Ui9Ev/1

    It's less efficient than using window functions but a lot more compatible and should work both in MySQL in MS Access too for instance.

    cheers,
    </wqw>

    p.s. Now I see that @Zvoni uses ORDER BY order_number DESC, order_date DESC while the db-fiddle above uses ORDER BY `order_date` DESC, `order_number` DESC which might be more correct IMO.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Quote Originally Posted by wqweto View Post
    Try this query without a window function (the ROW_NUMBER bit)

    https://www.db-fiddle.com/f/tZJbvdrRUNUrx8g18Ui9Ev/1

    It's less efficient than using window functions but a lot more compatible and should work both in MySQL in MS Access too for instance.

    cheers,
    </wqw>

    p.s. Now I see that @Zvoni uses ORDER BY order_number DESC, order_date DESC while the db-fiddle above uses ORDER BY `order_date` DESC, `order_number` DESC which might be more correct IMO.
    Argg...and additionally i missed that ROW_NUMBER is only available in MySQL8 and later, not earlier.
    Damn!

    wqweto, OP's Problem is, that he wants to create a View for that Statement, and MySQL5.6 and earlier doesn't permit Sub-Selects in Joins.
    OTOH, he could "export" each nested SELECT into its own View

    btw: I addressed the sorting criteria, too :-)
    but i noticed that order_number seems to be unique (kind of AUTO-INCREMENT)
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Got it without ROW_NUMBER
    sql Code:
    1. SET @rn = 0;
    2. SET @ic ='';
    3. SELECT
    4.         i.item_code,
    5.         i.item_description,
    6.         i.item_unit,
    7.         ip.order_number,
    8.         ip.order_date,
    9.         ip.price
    10. FROM inventory As i
    11. LEFT JOIN
    12. (SELECT            
    13.             order_number,
    14.             order_date,
    15.             price,
    16.             (@rn:=CASE
    17.                      WHEN @ic=item_code
    18.                      THEN @rn+1
    19.                      ELSE 1
    20.                    END) As RN,
    21.              @ic:=item_code As item_code
    22.         FROM inventory_purchases ORDER BY item_code ASC, order_date DESC, order_number DESC) As ip
    23. ON i.item_code=ip.item_code and ip.rn=1
    24. ORDER BY i.item_code
    No idea if you can use session variables in a view
    (EDIT: Probably not.
    A view definition is subject to the following restrictions:

    • The SELECT statement cannot contain a subquery in the FROM clause.
    • The SELECT statement cannot refer to system variables or user-defined variables.
    And reading the MySQL-Docs you cannot use user-variables in a View. Period!
    So either the OP uses wqweto's solution incl. "exporting" the nested SELECT's to be their own view, or upgrades to MySQL8.
    ...or doesn't use a view at all.....

    Inspiration from here: https://www.mysqltutorial.org/mysql-row_number/

    sqlfiddle: http://sqlfiddle.com/#!9/88acc0/1
    Last edited by Zvoni; Aug 8th, 2022 at 09:11 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    89

    Re: Mysql Nested sub query

    Thanks guys

    I cannot get any of the above to work. Guess it is time to upgrade to Mysql 8

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Quote Originally Posted by KenBZim2 View Post
    Thanks guys

    I cannot get any of the above to work. Guess it is time to upgrade to Mysql 8
    Probably your best bet
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Mysql Nested sub query

    I use MySQL 5.xx and the following should work if you still need a solution.

    Code:
    SELECT  `i`.`item_code`
    , `i`.`item_description`
    , `i`.`item_unit`
    , `ip`.`order_number`
    , `ip`.`order_date`
    , `ip`.`price`
    FROM `inventory` i
    INNER JOIN
    	`inventory_purchases` ip
    ON (`i`.`item_code` = `ip`.`item_code`)
    WHERE `ip`.`order_date` = (SELECT MAX(`order_date`) FROM `inventory_purchases` p2 WHERE `ip`.`item_code` = `p2`.`item_code`)
    ORDER BY `i`.`item_code`;

  13. #13
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,321

    Re: Mysql Nested sub query

    Same item_code can be ordered twice on the same date and in practice this happens all the time when operators remember they need a little bit extra of particular item_code later the same day they ordered it already.

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    .... and don't use INNER JOIN ... items from Inventory without inventory_purchases are discarded with INNER JOIN.
    Use LEFT JOIN
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Mysql Nested sub query

    wqweto, you are correct but the OP did not include that in their requirements, nor did they indicate that in their own sample SQL that they tried. Had those indicators been there, the SQL statement I used may have been different. At this point we don't know how to handle it because we don't know how the OP would want it handled.

    Had the OP needed the very last order from the last purchase date for each item, then I might have set the WHERE subquery to order by `purchase_id` DESC and then set a LIMIT 1 option as a starting point to test. This may have required an additional part to the WHERE clause. However, do you want the last order entered (by purchase_id) or the last order entered (by order_no) or the first order of the day or the largest order by quantity, or dollars? The above should give the OP a starting point to modify.

  16. #16
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Mysql Nested sub query

    Quote Originally Posted by Zvoni View Post
    .... and don't use INNER JOIN ... items from Inventory without inventory_purchases are discarded with INNER JOIN.
    Use LEFT JOIN
    Zvoni, i interpreted the OP's question with the SQL statement I created. Yes, you are correct, a LEFT JOIN would be a better option if the OP wanted all items, and the last purchase for each if applicable. But I didn't see that in Post #1. The OP will have to be careful about using any WHERE that uses fields from the second table in the join of the outer query because that will force the join back to an INNER JOIN without some special handing of NULL values.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Quote Originally Posted by rasinc;[URL="tel:5577077"
    5577077[/URL]]Zvoni, i interpreted the OP's question with the SQL statement I created. Yes, you are correct, a LEFT JOIN would be a better option if the OP wanted all items, and the last purchase for each if applicable. But I didn't see that in Post #1. The OP will have to be careful about using any WHERE that uses fields from the second table in the join of the outer query because that will force the join back to an INNER JOIN without some special handing of NULL values.
    Rasinc,
    look closely at my left join, especially the rownumber RN. I filter in the ON-clause
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Mysql Nested sub query

    Zvoni, I do admit that when I saw the use of Partition, I did not dissect your query very closely. I knew Partition was not useable in version 5.7 and i have not read much about version 8 yet. So because this thread had already determined the versions I did not look that closely, sorry.

    I have used ON to filter but I don't think I have ever checked to see if it avoids forcing an INNER JOIN when selecting from the right table. Thank you for bringing that to my attention.

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,508

    Re: Mysql Nested sub query

    Quote Originally Posted by rasinc View Post
    Zvoni, I do admit that when I saw the use of Partition, I did not dissect your query very closely. I knew Partition was not useable in version 5.7 and i have not read much about version 8 yet. So because this thread had already determined the versions I did not look that closely, sorry.

    I have used ON to filter but I don't think I have ever checked to see if it avoids forcing an INNER JOIN when selecting from the right table. Thank you for bringing that to my attention.
    See also this discussion: https://www.vbforums.com/showthread....n-WHERE-Clause
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width