[blakekp]

Hello,

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

[unknown500]

I think you have one extra AS in your INNER JOIN SQL statement.
If you remove it, the code should work.

INNER JOIN ((Select drg.drug ID AS FROM drug AS drg)

[blakekp]

omg, thank you i am an idiot, been staring at this for too long i suppose

[unknown500]

blakekp, on 10 March 2013 - 12:04 PM, said:

omg, thank you i am an idiot, been staring at this for too long i suppose

No problem. I stared at something for hours the other day. Then, realized that I was missing a semi-colon.