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.
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...