Click here to Skip to main content
15,040,743 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Table: drug_catalog
drug_id drug_price date_effective
id1 1002017-01-01
id12002018-01-01
id13002019-01-01
id25002017-01-01
id26002020-02-01




Table: order
drug_id customer_id date_order
id1 5552017-01-02
id25552018-01-02
id16662019-01-02
id15552018-01-02
id27772020-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




Expected result
b.drug_ida.customer_id b.drug_price a.date_order
id1555 1002017-01-02
id2555 5002018-01-02
id1666 3002019-01-02
id1555 2002018-01-02
id2777 6002020-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

1 solution

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...
   
Comments
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.
https://drive.google.com/open?id=1BQwrMue4K6PNovYI8QZ3pGtZFKi3-CLi
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
   
Glad you got it solved.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900