mysql - 基于 CASE 的左连接

Question

我想在 MySQL 中进行左连接。连接基于一个条件。例如，如果值为328，则use Left Join <table A> ELSE使用Left Join <table B>

以下是查询：

SELECT * FROM applications app
CASE 
WHEN 
    app.application_type = 328 
THEN
    LEFT JOIN application_individu ai 
    ON app.id = ai.application_id 
ELSE
    LEFT JOIN application_org ao 
    ON app.id = ao.application_id 
END
WHERE app.id=11

我收到一条错误消息phpmyadmin

#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 
'(CASE WHEN app.application_type = 328 THEN
 LEFT JOIN application_individu a' at line 5

1. 可以CASE加入吗？

2.如果可能的话，如何以正确的方式进行？

更新数据

以下是样本数据。

应用

id  application_type  app_reference  applicant_type  status created_date
1   330               ZAK00001       328             1      2019-01-16 16:45:14
2   330               ZAK00002       328             1      2019-01-17 12:31:14
3   330               ZAK00003       329             2      2019-01-17 14:31:40

application_individu

id   application_id  name  age  gender  contactNo  ic
1    1               Ali   30   Male    1234       12345678
2    2               Chan  45   Male    1234       54321

application_org

id   application_id  companyName  companyRegNo  picName  picContactNo  companyEmail
1    3               My StartUp   x-123Y        Raja     01035562      [e-mail]

参数

id  parameterValue  detail
1   328             Personal              
2   329             Company
3   1               Complete
4   2               Pending

这是我更新后的查询，没有使用CASE.

SELECT * FROM applications app
LEFT JOIN application_individu ai ON app.id=ai.application_id
LEFT JOIN application_org ao ON app.id=ao.application_id
LEFT JOIN parameters param ON app.applicant_type=param.main_id
LEFT JOIN parameters param2 ON app.status=param2.main_id
LEFT JOIN parameters param3 ON app.applicant_type=param3.main_id
LEFT JOIN parameters param4 ON app.status=param4.main_id

有没有办法检查应用程序是否存在，Personal这样Company我就不会重复加入的参数。

标签： mysqlsql

Answer 1

你可以使用联合

   SELECT app.* FROM applications app    
        LEFT JOIN application_individu ai 
        ON app.id = ai.application_id
    where app.application_type = 328 
union 
SELECT app.* FROM applications app    
       LEFT JOIN application_org ao 
       ON app.id = ao.application_id 
    where app.application_type != 328