15,040,743 members
2.00/5 (1 vote)
See more:
 drug_id drug_price date_effective id1 100 2017-01-01 id1 200 2018-01-01 id1 300 2019-01-01 id2 500 2017-01-01 id2 600 2020-02-01

 drug_id customer_id date_order id1 555 2017-01-02 id2 555 2018-01-02 id1 666 2019-01-02 id1 555 2018-01-02 id2 777 2020-03-01

Hello everyone,I have two table are LEFT JOIN and I need to show drug_price of date_effective
1. Table drug_catalog
2. Table order

 b.drug_id a.customer_id b.drug_price a.date_order id1 555 100 2017-01-02 id2 555 500 2018-01-02 id1 666 300 2019-01-02 id1 555 200 2018-01-02 id2 777 600 2020-03-01

What I have tried:

SQL
`SELECT b.drug_id,a.customer_id,b.drug_price,a.date_order FROM order a LEFT JOIN drug_catalog b ON a.drug_id = b.drug_id WHERE a.date_order >= b.date_effective LIMIT 1`
Posted
Updated 10-Jan-19 8:30am

## Solution 1

If I understand your question correctly, perhaps something like the following which uses correlated subquery. I assume that the date effective means effective until. If it's effective from then the greater than condition must be less than and MIN needs to be replaced with MAX.
SQL
```SELECT b.drug_id,
a.customer_id,
b.drug_price,
a.date_order
FROM           order        a
LEFT JOIN drug_catalog b ON a.drug_id = b.drug_id
WHERE b.date_effective = (SELECT MIN(b2.date_effective)
FROM   drug_catalog b2
WHERE  b2.drug_id        =  a.drug_id
AND    b2.date_effective >= a.date_order)```

Just wondering if the order of the tables in the LEFT JOIN is correct...
terzasek 10-Jan-19 23:40pm

Hello Thank you for help. But this query like focus Max of each drug_price.
Could you please send me more suggestion.
Thank you.
Wendelius 11-Jan-19 0:04am

So I take it the date_effective means effective from?

If that is the case, as said change the condition in the subquery. In other words
```SELECT b.drug_id,
a.customer_id,
b.drug_price,
a.date_order
FROM           order        a
LEFT JOIN drug_catalog b ON a.drug_id = b.drug_id
WHERE b.date_effective = (SELECT MAX(b2.date_effective)
FROM   drug_catalog b2
WHERE  b2.drug_id =  a.drug_id
AND    b2.date_effective <= a.date_order)```
terzasek 11-Jan-19 0:36am

I'm so sorry but this's only way to explained the expected result please check this link. Thank you.
CHill60 11-Jan-19 4:00am

I am getting weary of telling you not to post links to pictures - put the information into the question. Many sites (such as the one above) are blocked by corporate firewalls. We help in this forum in our spare time - often at work.
By not heeding my advice you are just reducing the number of people who can help.
Also be aware that there is an Improve Question link on your post - don't post a new question just to add information
terzasek 11-Jan-19 7:34am

OK,I understand.
Wendelius 11-Jan-19 12:44pm

Did you try the second query I wrote? As far as I can see it gives the result described in the question, or am I missing something?
terzasek 11-Jan-19 20:38pm

Yes i try it and I thought this query is wrong but actually it's working.
I have to admit that I was wrong.
Wendelius 12-Jan-19 9:35am