0

I have 2 tables

orders
+----+-------+--------+------------+
| id | email | status | date       |
+----+-------+--------+------------+
|  1 |       |      0 | 1427953986 |
|  2 |       |      0 | 1427954771 |
|  5 |       |      0 | 1427957427 |
+----+-------+--------+------------+

price
+----+----+-----+--------+----+-----+------+-------+------------+
| id | wd | bts | family | r  | tlv | kids | user  | date       |
+----+----+-----+--------+----+-----+------+-------+------------+
|  1 |  0 |   0 |      0 | 75 | 65  | 50   | int   | 1425894693 |
|  9 | 59 |   0 |      0 | 75 | 65  | 50   | mordi | 1435666477 |
| 10 | 59 |   0 |      0 | 75 | 65  | 50   | zack  | 1438790306 |
| 11 | 59 |   0 |      0 | 75 | 65  | 50   | zack  | 1438790326 |
| 12 | 59 |   0 |      0 | 75 | 65  | 50   | zack  | 1438790468 |
+----+----+-----+--------+----+-----+------+-------+------------+

How can I left join price to orders on the right date. The result should join to orders table the row from price table where the date of the order is grater or equal to date on price, but not greater then the next row (if exists) for example

Example
+-----------+-------+--------+------------------+----------+----+-----+--------+----+-----+------+-------+--------------+
| orders.id | email | status | order.date       | price.id | wd | bts | family | r  | tlv | kids | user  | price.date       |
+-----------+-------+--------+------------------+----------+----+-----+--------+----+-----+------+-------+--------------+
|  1        |       |      0 | 1425894699       |  1       |  0 |   0 |      0 | 75 | 65  | 50   | int   | 1425894693   |
|  2        |       |      0 | 1438790336       |  11      |  0 |   0 |      0 | 75 | 65  | 50   | zack  | 1438790326   |
|  5        |       |      0 | 1438790478       |  12      |  0 |   0 |      0 | 75 | 65  | 50   | zack  | 1438790468   |
+-----------+-------+--------+------------------+----------+----+-----+--------+----+-----+------+-------+--------------+

closed as unclear what you're asking by ypercubeᵀᴹ, Colin 't Hart, Shanky, dezso, Max Vernon Sep 29 '15 at 14:44

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • How does the Order Table and Price table link ? via Id? – mouliin Sep 29 '15 at 8:04
  • no, I based on date I want to the to price of the order to reflect the right price at that time – phper Sep 29 '15 at 8:06
  • I don't understand the relation between your tables. if you are still in the dev phase, I would recommend that you create FromDate and ToDate columns for price and then on your left outer join you can use a between clause. if its too late to fiddle around with tables, view or temp table is your other option. – mouliin Sep 29 '15 at 9:07
  • unfortunately this is an active system, there is no going back, I will rephrase my question – phper Sep 29 '15 at 9:09
  • 2
    And by the way, these are not dates. – ypercubeᵀᴹ Sep 29 '15 at 9:25
2

There is a mismatch between the data and results in your question but I think you need a query like this. It's not going to be very efficient if the orders table is big:

SELECT 
    o.*,            -- pick the columns you want
    p.*             -- in the result 
FROM orders AS o
  LEFT JOIN price AS p
    ON p.date =
       ( SELECT pi.date
         FROM price AS pi
         WHERE pi.date <= o.date
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

Tested (with modified data in the orders table) in: SQLfiddle.

  • this did not work properly, I realize now there is no real way to achieve this I will be forced to change my structure, thanks for trying – phper Sep 29 '15 at 11:20
  • @phper, what does "not work properly" mean? – ypercubeᵀᴹ Sep 29 '15 at 11:34
  • this LEFT JOIN is only 1 to 1 instead of 1 to many, isn't? – neobie Aug 30 '18 at 10:01
  • @neobie correct. The LIMIT 1 ensures there is at most one row from price joined to a row in orders - well, assuming that there is a UNIQUE constraint on price (date). – ypercubeᵀᴹ Aug 30 '18 at 14:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.