CROSS/OUTER APPLY in MySQL

by

Your closest direct approximation is a join with a correlated sub-query as the predicate.

SELECT
   ORD.ID
  ,ORD.NAME
  ,ORD.DATE
  ,ORD_HISTORY.VALUE
FROM
  ORD
INNER JOIN
  ORD_HISTORY
    ON  ORD_HISTORY.<PRIMARY_KEY>
        =
        (SELECT ORD_HISTORY.<PRIMARY_KEY>
           FROM ORD_HISTORY
          WHERE ORD.ID = ORD_HISTORY.ID
            AND ORD.DATE <= ORD_HISTORY.DATE
       ORDER BY ORD_HISTORY.DATE DESC
          LIMIT 1
        )

In your case, however, you only need one field from the target table. This means that you are able to use the correlated sub-query directly in the SELECT statement.

SELECT
   ORD.ID
  ,ORD.NAME
  ,ORD.DATE
  ,(SELECT ORD_HISTORY.VALUE
      FROM ORD_HISTORY
     WHERE ORD.ID = ORD_HISTORY.ID
       AND ORD.DATE <= ORD_HISTORY.DATE
  ORDER BY ORD_HISTORY.DATE DESC
     LIMIT 1
   )   AS VALUE
FROM
  ORD

More Related Contents:

  1. Getting the SUM() of claps and at the same time counting bookmarks in one query
  2. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  3. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  4. How can I do three table JOINs in an UPDATE query?
  5. 1052: Column ‘id’ in field list is ambiguous
  6. Join tables with SUM issue in MYSQL
  7. MySQL update table based on another tables value
  8. MySQL join with where clause
  9. How can I join multiple SQL tables using the IDs?
  10. JOIN queries vs multiple queries
  11. Why does MySQL report a syntax error on FULL OUTER JOIN?
  12. conditional join in mysql
  13. MYSQL Select MAX Date inside a join statement
  14. a count for each join – optimisation
  15. Combine Multiple child rows into one row MYSQL
  16. Subqueries with EXISTS vs IN – MySQL
  17. Cumulative sum over a set of rows in mysql
  18. When to use STRAIGHT_JOIN with MySQL
  19. MySQL – SELECT WHERE field IN (subquery) – Extremely slow why?
  20. MySQL update a joined table
  21. MySQL join query using like?
  22. How to find non-existing data from another Table by JOIN?
  23. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  24. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  25. mySQL – Create a New Table Using Data and Columns from Three Tables
  26. Count with IF condition in MySQL query
  27. Mysql select where not in table
  28. MySQL how to join tables on two fields
  29. Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row
  30. How to store a one to many relation in MySQL database?

Leave a Comment