求大神， mysql 根据条件用不同字段连表问题

[chggg]

， order 表有 drug_provider_id ， provider_id ， order_type 字段，当 order_type=3 ， provider_id 取 drug_provider_id ，否则取 provider_id 。 provider 表有 id 字段，
我要用字段
if(t1.order_type = 3 ,t1.drug_provider_id,t1.provider_id) as provider_id 去左连 provider 表的 ID 字段具体语句怎么写

[chggg]

应该是 order 表是主表， provider 表要去左连 order 表，还有其它表要左连 order 表

[txoooy]

用 union 吧, 举个例子:

"""
SELECT
o.id AS order_id,
p. NAME AS provider_name
FROM
test.order o
INNER JOIN test.provider p ON o.order_type = 3
AND o.provider_Id = p.id

UNION

SELECT
o.id AS order_id,
p. NAME AS provider_name
FROM
test.order o
INNER JOIN test.drug_provider p ON o.order_type != 3
AND o.drug_provider_id = p.id
"""