Further to this, I wonder if you could help me out with a query that uses these tables;
Create table drugs
(
drugref varchar (5),
name varchar(120),
DosageMG varchar(120),
PackSize int
)
GO
INSERT INTO Drugs
SELECT 'A1', 'Aspirin', '75', 10
UNION ALL
SELECT 'B2', 'Salbutamol', '100',20
UNION ALL
SELECT 'C3', 'Tramadol', '150',10
UNION ALL
SELECT 'D4', 'Warfarin', '5',20
UNION ALL
SELECT 'E5', 'Bisoprolol', '5',10
union all
SELECT 'F6', 'Paracetamol', '200',20
union all
SELECT 'G7', 'Paracetamol', '300',20
UNION all
select 'H8', 'Atorvastatin', '80',28
union all
select 'I9', 'Folic Acid', '25',14
union all
select 'J10', 'Ramipril', '2.5',28
Go
Create Table Doctor
(
doctorref varchar (5),
doctorname varchar (120),
location varchar (120)
)
GO
INSERT INTO Doctor
SELECT 'A1', 'Smith', 'London'
union all
select 'B2', 'Jones', 'Glasgow'
union all
SELECT 'C3', 'Campbell', 'Manchester'
Union all
Select 'D4', 'Ward', 'Birmingham'
Union all
Select 'E5', 'Perry', 'Cardiff'
Go
Create Table Doctor_Prescribed_Drugs
(
doctorref varchar (5),
drugref varchar (5)
)
Go
INSERT INTO Doctor_Prescribed_Drugs
SELECT 'A1', 'G7'
UNION ALL
SELECT 'A1', 'A1'
UNION ALL
SELECT 'A1', 'E5'
UNION ALL
SELECT 'A1', 'I9'
UNION ALL
SELECT 'C3', 'A1'
UNION ALL
SELECT 'C3', 'E5'
GO
I've been trying to write a query using joins using the Doctor_Prescribed_Drugs table, as this is the only table that contains relational data.
Here's my query so far:
select d.drugname, d.packsize,
doc.name, doc.location
from drugs d
left join doctor_prescribed_drugs dpd on d.drugref = dpd.drugref
left join doctor_prescribed_drugs dpd on doc.doctorref = dpd.doctorref
where d.drugref in ('A1', 'B2', 'C3')
but this is erroring out with "Msg 1011, Level 16, State 1, Line 1
The correlation name 'dpd' is specified multiple times in a FROM clause."
I can't get the data back that I need (the Doctor name, location and what they've prescribed etc) without using the dpd tabe, but I can't figure out the query that will do this. Pretty much everything I've tried so far has broken.
I'm sure that I'm just being stupid here... either that or I'm too new...
Thanks for any help. It's always appreciated.
PS: Jeff, I hope I got this formatted right this time!