左连接两个表，多列 - Left joining two tables, multiple columns

Left joining two tables, multiple columns

本文关键字：两个多列连接 | 更新日期: 2023-09-27

我需要左连接两个表：Cardealers和Applications。

我想看看哪些汽车经销商收到了哪些申请。

每个应用程序被转发给三个汽车经销商，因此我的表结构有这 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 ."'

编辑：最好为每个汽车经销商的列提供别名