Click here to Skip to main content
13,899,467 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Consider below table: Item: b , table: Order a
id   price     date          |   id     cid     date
1    100    2018-01-01       |   1      000    2018-02-15
1    200    2018-02-01       |   1      111    2019-01-01
1    300    2019-03-01       |   2      000    2017-04-01
2    100    2017-04-01       |   1      333    2020-01-01
2    200    2019-05-01       |   3      222    2018-05-01
3    300    2018-06-01       |   1      000    2020-01-01
3    400    2018-03-01       |

How can i get Order price each date like so:
a.id    a.cid     b.price      a.date
1       000        200         2018-02-15
1       111        200         2019-01-01
2       000        100         2017-04-01
1       333        300         2020-01-01
3       222        400         2018-05-01
1       000        300         2020-01-01


What I have tried:

SELECT a.id,a.cid,b.price,a.date FROM  Order a
LEFT JOIN Item b ON a.id = b.id
WHERE a.date = (SELECT MIN(b2.date)
FROM   Item b2
WHERE  b2.id  =  a.id 
AND    b2.date >= a.date)
Posted
Updated 11-Jan-19 7:35am
Comments
Richard Deeming 11-Jan-19 13:26pm
   
REPOST
This is a slightly-reworded version of the question you asked yesterday:
https://www.codeproject.com/Questions/1274095/LEFT-JOIN-two-table-each-period-of-date[^]

If you want to edit your question to add more information, click the green "Improve question" link and update your question. Don't post the update as a new question.
terzasek 11-Jan-19 20:00pm
   
Ok, i will remove one of theme.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

As far as I can see this is the same query as in your previous questoin, just renamed columns.

In other words
SELECT a.id,
       a.cid,
       b.price,
       a.date
FROM         order a      
   LEFT JOIN item  b ON a.id = b.id
WHERE b.date = (SELECT MAX(b2.date)
                FROM   item b2
                WHERE  b2.id   =  a.id
                AND    b2.date <= a.date)
   
Comments
terzasek 11-Jan-19 20:29pm
   
Thank you very much, this's work.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190306.1 | Last Updated 11 Jan 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100