mysql - 如果条件为 true，则按 id 进行连接查询

Question

我是 SQL 新手，我想知道一些带有 Join 条件的查询，我的问题是我需要检查一些条件，如果 true 则采用左连接的 id，或者如果 false 则采用另一个 id 并连接表格

select 
 coalesce(supplier.NAME,PARTY2_supplier,PARTY1_supplier)  as Supplier,
     from ACCOUNT acc
     left join RELATIONSHIP r on r.RELATIONSHIP_ID = 2
     left join ORGANIZATION supplier on supplier.ORGANIZATION_ID = acc.SUPPLIER_ID 

if (r.name = 'sales' or r.name = 'purchase'){

        left join ORGANIZATION PARTY2_supplier on PARTY2_supplier.ORGANIZATION_ID = r.PARTY2_ID

}
else {

        left join ORGANIZATION PARTY1_supplier on PARTY1_supplier.ORGANIZATION_ID = r.PARTY1_ID

}
        where acc.ACCOUNT_ID = 34;

我需要一个带有该帐户 ID 的正确供应商名称

Answer 1

快速回答是:您必须连接所有表并使用 if功能。

if函数以如下方式工作:
它将返回 yes用于下一个查询 select if(500 < 1000, "yes", "no");

有了这些知识，你就可以做这样的事情:

select
if (r.name = 'sales' or r.name = 'purchase', PARTY2_supplier.NAME, PARTY1_supplier.NAME) as Supplier
from ACCOUNT acc
left join RELATIONSHIP r on r.RELATIONSHIP_ID = 2
left join ORGANIZATION supplier on supplier.ORGANIZATION_ID = acc.SUPPLIER_ID 
left join ORGANIZATION PARTY1_supplier on PARTY1_supplier.ORGANIZATION_ID = r.PARTY1_ID
left join ORGANIZATION PARTY2_supplier on PARTY2_supplier.ORGANIZATION_ID = r.PARTY2_ID
where acc.ACCOUNT_ID = 34
;