php - 左联接两个表，多列

我需要离开联接两个表：Cardealers和Applications。

我想看看哪些cardealers收到了哪些申请。

每个应用程序都转发给三个cardealer，因此我的表结构具有以下3列：receiver_1，receiver_2，receiver_3。

接收方列中的ID与cardealer_id相同。

我的这段代码仅适用于一栏和一栏：

$res1 = $wpdb->get_results( " SELECT leads.*, cardealers.*
    FROM leads
    LEFT JOIN cardealers ON leads.receiver_1 = cardealers.cardealer_id
    WHERE leads.receiver_1 = cardealers.cardealer_id
    AND cardealers.cardealer_id = '". $cardealer_id ."'" );

$res2 = $wpdb->get_results( " SELECT leads.*, cardealers.*
    FROM leads
    LEFT JOIN cardealers ON leads.receiver_2 = cardealers.cardealer_id
    WHERE leads.receiver_2 = cardealers.cardealer_id
    AND cardealers.cardealer_id = '". $cardealer_id ."'" );

$res3 = $wpdb->get_results( " SELECT leads.*, cardealers.*
    FROM leads
    LEFT JOIN cardealers ON leads.receiver_3 = cardealers.cardealer_id
    WHERE leads.receiver_3 = cardealers.cardealer_id
    AND cardealers.cardealer_id = '". $cardealer_id ."'" );

如您所见，我执行了3个独立的语句，所有语句均有效。但我想将这些合并为一个陈述。

我该怎么做呢？

最佳答案

只需继续添加表并为其别名。

SELECT
   leads.*, cardealers1.*, cardealers2.*, cardealers3*
FROM leads
LEFT JOIN cardealers as cardealers1
   ON leads.receiver_1 = cardealers1.cardealer_id
LEFT JOIN cardealers as cardealers2
   ON leads.receiver_2 = cardealers2.cardealer_id
LEFT JOIN cardealers as cardealers3
   ON leads.receiver_3 = cardealers3.cardealer_id
WHERE
   leads.receiver_1 = cardealers1.cardealer_id
   AND leads.receiver_2 = cardealers2.cardealer_id
   AND leads.receiver_3 = cardealers3.cardealer_id
   AND cardealers.cardealer_id = '". $cardealer_id ."'

编辑：最好为每个cardealers的列提供别名