3 Replies - 2098 Views - Last Post: 10 March 2013 - 12:10 PM Rate Topic: -----

#1 blakekp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-February 13

ACCESS SQL, NESTED SELECT/JOIN

Posted 10 March 2013 - 08:28 AM

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 :D

Is This A Good Question/Topic? 0
  • +

Replies To: ACCESS SQL, NESTED SELECT/JOIN

#2 unknown500  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 30
  • Joined: 11-June 12

Re: ACCESS SQL, NESTED SELECT/JOIN

Posted 10 March 2013 - 12:01 PM

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)
Was This Post Helpful? 0
  • +
  • -

#3 blakekp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-February 13

Re: ACCESS SQL, NESTED SELECT/JOIN

Posted 10 March 2013 - 12:04 PM

omg, thank you i am an idiot, been staring at this for too long i suppose
Was This Post Helpful? 0
  • +
  • -

#4 unknown500  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 30
  • Joined: 11-June 12

Re: ACCESS SQL, NESTED SELECT/JOIN

Posted 10 March 2013 - 12:10 PM

View Postblakekp, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1