LinksUp (8/18/2013)
I think this is what you are after:
select d.name, d.packsize, doc.doctorname, doc.location
from Doctor_Prescribed_Drugs dpd
left join Doctor doc on dpd.doctorref = doc.doctorref
left join Drugs d on dpd.drugref = d.drugref
where dpd.drugref in ('A1', 'B2', 'C3')
If you compare your query with the above query, you will see the problem right away! By using the Doctor_Prescribed_Drugs table as the primary table, or linking (my term) table, it makes it easy to see how the left joins are to be constructed.
This works and the explanation fits too, but why LEFT JOIN to the link table? dpd shouldn't contain any doctors which aren't in the doctors table, or drugs which aren't in the drugs table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden