Multiple inner joins in pymysql

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_IDnFROM ‘ at line 2”)

Answer

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

Advertisement

Answer