I am attempting to select multiple columns from three tables in pymysql, and inner join them. I don't understand what is going wrong when I add the second inner join. I feel like it's a simple syntax mistake, but I just can't see it.
This works.
q1='''SELECT a.SUBJECT_ID, a.HADM_ID, a.ADMITTIME, p.GENDER, p.DOB, c.ITEMID,
FROM ADMISSIONS as a INNER JOIN PATIENTS as p ON a.SUBJECT_ID=p.SUBJECT_IDd;'
res1=curs.execute(q1)
curs.fetchall()
This doesn't work, and I can't see the mistake.
q1='''SELECT a.SUBJECT_ID, a.HADM_ID, a.ADMITTIME, p.GENDER, p.DOB, c.ITEMID,
FROM ADMISSIONS as a INNER JOIN PATIENTS as p ON a.SUBJECT_ID=p.SUBJECT_ID
FROM CHARTEVENTS as c INNER JOIN ON ADMISSIONS as a c.SUBJECT_ID=a.SUBJECT_ID;'''
res1=curs.execute(q1)
curs.fetchall()
The resulting error
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM ADMISSIONS as a INNER JOIN PATIENTS as p ON a.SUBJECT_ID=p.SUBJECT_ID\nFROM ' at line 2")
MySQL error messages are very exact. They point directly to the (first) problem.
Remove the comma
c.ITEMID, FROM ADMISSIONS
^-------------------------------here
But after the join you try to select from another table. That won't work. You have to join them all.
FROM ADMISSIONS as a
INNER JOIN PATIENTS as p ON a.SUBJECT_ID=p.SUBJECT_ID
FROM CHARTEVENTS as c <------------------------------------------NO!
INNER JOIN ON ADMISSIONS as a c.SUBJECT_ID=a.SUBJECT_ID