Python MySQL表联接

Question

我试图在python查询中对多个表进行外部联接

（外部联接，因为某些字段具有空数据）

我正在使用以下命令：

sql = "SELECT \
  CATINFO.Chip_ID AS Cat, \
  CATDIET.Diet_Req AS Diet, \
  CATMEDICAL.Med_his AS Med\
  FROM CATINFO \
  OUTER JOIN CATDIET, CATREQ, CATMEDICAL ON CATINFO.ID = CATDIET.Chip_ID, CATREQ.Chip_ID, CATMEDICAL.Chip_ID"

我得到以下错误输出：

mysql.connector.errors.ProgrammingError: 1064 (42000):
'OUTER JOIN CATDIET, CATREQ, CATMEDICAL ON CATINFO.Chip_ID = CATDIET.Chip_ID, CATREQ.Chip_ID, CATMEDICAL.Chip_ID'

我不确定我到底哪里出错了

标识的表格格式应如下例所示：

Chip_ID   diet         req           med 
1         low carb     null          broken leg
2         null         belly rubs    null

Answer 1

sql = "SELECT \
  CATINFO.Chip_ID AS Cat, \
  CATDIET.Diet_Req AS Diet, \
  CATMEDICAL.Med_his AS Med, \
  CATREQ.Add_Req AS Req \
  FROM CATINFO \
  LEFT JOIN CATDIET ON CATINFO.Chip_ID = CATDIET.Chip_ID \
  LEFT JOIN CATMEDICAL ON CATINFO.Chip_ID = CATMEDICAL.Chip_ID \
  LEFT JOIN CATREQ ON CATINFO.Chip_ID = CATREQ.chip_ID "

Answer 2

我会猜出列名（因为你没有清楚地提到它们）。正确的外部联接是这样写的：

select
  i.chip_id as cat_id,
  d.diet_req as diet,
  r.req_name as req,
  m.med_his as med
from catinfo i
left join catdiet d on d.chip_id = i.id
left join catmedical m on m.chip_id = i.id
left join catreq r on r.chip_id = i.id

根据需要更改列名