mysql - 条件连接 MySQL

Question

我有这个SQL语句

SELECT f.form_id
     , f.tba_id
     , f.patient_id
     , f.location_code
     , f.date_created
     , p.lmp
     , p.first_name
     , p.last_name
     , p.phone
     , u.phone 
  FROM forms f 
  JOIN patients p 
    ON f.patient_id = p.id 
  JOIN users u 
    ON f.tba_id = u.id 
 WHERE f.patient_id = 20

返回以下 json

[
{
    "form_id": "11",
    "tba_id": "10",
    "patient_id": "20",
    "location_code": "",
    "date_created": "2014-08-30 16:46:16",
    "lmp": "",
    "first_name": "Mariam",
    "last_name": "Oti",
    "phone": "2348061356894"
},
{
    "form_id": "12",
    "tba_id": "10",
    "patient_id": "20",
    "location_code": "",
    "date_created": "2014-08-30 17:02:26",
    "lmp": "",
    "first_name": "Mariam",
    "last_name": "Oti",
    "phone": "2348061356894"
}
]

我还想从 f.form_id 上的另一个表加入，但警报表上并不总是有每个 form_id 的记录，即如果我执行该语句

SELECT status
     , cug_id
     , alert_id 
  FROM alert 
 WHERE form_id = 11 

和

SELECT status
     , cug_id
     , alert_id 
  FROM alert 
 WHERE form_id = 12

我可能会为第一个语句获取一条记录，而为第二个语句获取 0 条记录。

我的目标是得到这样的结果

[
{
    "form_id": "11",
    "tba_id": "10",
    "patient_id": "20",
    "location_code": "",
    "date_created": "2014-08-30 16:46:16",
    "lmp": "",
    "first_name": "Mariam",
    "last_name": "Oti",
    "phone": "2348061356894",
    "status": "0",
    "cug_id": "17", 
    "alert_id": "20"
},
{
    "form_id": "12",
    "tba_id": "10",
    "patient_id": "20",
    "location_code": "",
    "date_created": "2014-08-30 17:02:26",
    "lmp": "",
    "first_name": "Mariam",
    "last_name": "Oti",
    "phone": "2348061356894",
    "status": "",
    "cug_id": "", 
    "alert_id": ""
}
]

因此，当我尝试进行连接时，我最终得到了一条记录

Answer 1

对警报表使用左连接

SELECT f.form_id
     , f.tba_id
     , f.patient_id
     , f.location_code
     , f.date_created
     , p.lmp
     , p.first_name
     , p.last_name
     , p.phone
     , u.phone 
     , a.status
     , a.cug_id
     , a.alert_id 
  FROM forms f 
  JOIN patients p 
    ON f.patient_id = p.id 
  JOIN users u 
    ON f.tba_id = u.id 
  left join alert a
    on a.form_id = f.form_id
 WHERE f.patient_id = 20