选择表 1 中的所有行并标记表 2 中存在的行 [英] select all rows from table 1 and mark rows that exist in table 2

问题描述

我有一张供应商表(ID、姓名、地址)

I have a table of suppliers (id, name, address)

我有另一个表将供应商与客户联系起来(accredited_suppliers-> supply_id, customer_id)

I have another table linking suppliers with customers (accredited_suppliers-> supplier_id, customer_id)

我想选择所有带有标识符的供应商，该标识符显示是否与客户有关系 (customer_id)

I want to select all suppliers with an identifier showing if there is a relationship with the customer (customer_id)

即结果如下:

供应商:

id, name, address, customer_link
1, name1, address1, null
2, name2, address2, Yes
3, name3, address3, null
4, name4, address4, Yes

更多信息:

供应商表:ID名称......

Supplier Table: id name ... ...

客户表:ID名称......

Customer Table: id name ... ...

AccreditedSuppliers 表:ID客户ID供应商编号

AccreditedSuppliers Tables: id customer_id supplier id

例如我想要所有供应商，有一列标记所有与客户 ID=100 相关联的供应商

e.g. I want all suppliers, with a column flagging all suppliers linked to customer id=100

推荐答案

SELECT t1.*, IF(count(`t2`.`customer_id`) > 0, 'yes', NULL) as customer_link
FROM `suppliers` AS `t1`
LEFT JOIN `accredited_suppliers` AS `t2` ON `t2`.`supplier_id` = `t1`.`id`

这篇关于选择表 1 中的所有行并标记表 2 中存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！