so I just need to nest select statements in my from clause to increase efficiency on joins, it seems as though I'm missing something in the following code(I'm new to SQL, and computer programming in general) I keep getting an error on syntax so I'm figuring someone with some time under their belt with this will school me right quick!
SELECT phys.[Physician State], Count.DISTINCT(phys.[Physician ID]) AS [CountOfPhysician ID], Count.DISTINCT(script.DateFilled) AS CountOfDateFilled, Sum(script.[Prescription Units Dispensed]) AS [SumOfPrescription Units Dispensed] FROM ( Select phys.Physician ID, physPhysician State FROM Physicians AS phys) INNER JOIN ((Select drg.drug ID AS FROM drug AS drg) INNER JOIN (select drug id, physician ID from Prescription As Script) ON drg.[Drug ID] = Script.[Drug ID]) ON phys.[Physician ID] = Script.[Physician ID] GROUP BY Drug.Class, Physicians.[Physician State];
the error is specifically highlighting the drg.drug ID in (Select drg.drug ID AS FROM drug AS drg), and if i play with it it just cycles from drug to drug in it, the table names are all correct(aside from capitalization which i also illogically tried just in case haha)
for some reason I cant find a specific instance where multiple fields are extracted in a nested select statement and then used in a join, so I decided to post,
I thank you all for your help in advance!!!!!!!! yall helped me learn java lets hope the same occurs for sql