根据表字段值left join 不同的表

首先说下思路吧，捣鼓了好半天，网上找到解决办法，大概两种方法：
1.在left join 的后面将两张表union all连接为一张表，并加上“区分表关键字”，然后根据“区分表关键字”筛选我们需要的数据
2.使用两个left join, 每个left join 跟上一张表，并加上“区分表关键字”，然后在 on 条件后使用“区分表关键字”

重点

• left join 后面只能跟一张表，不能使用if，when case 啥的
• 添加“区分表关键字”进行条件判断

下面是SQL语句
第一种：一个left join 将两张表union all

SELECT 
ord.*,
odtl_goods_id, odtl_goods_type, odtl_quantity,
con_company_name,
mer_company,
goods.goodsName, goods.goodsPicture
FROM `order` as ord 
LEFT JOIN order_detail as odtl on odtl_order_id=ord.order_id
LEFT JOIN consumer as con on con.con_id=ord.order_con_id 
LEFT JOIN merchant as mer on mer.mer_id=ord.order_mer_id
LEFT JOIN (
    SELECT 1 AS goodsType, sh_id AS goodsId, sh_picture AS goodsPicture, sh_name AS goodsName FROM server_hosting
    UNION ALL
    SELECT 2 AS goodsType, sr_id AS goodsId, sr_picture AS goodsPicture, sr_name AS goodsName FROM server_rental
) AS goods ON goods.goodsType=odtl.odtl_goods_type AND goods.goodsId=odtl_goods_id
sr.sr_id=odtl.odtl_goods_id)
WHERE ord.order_id=1

第一种：使用两个left join

SELECT 
ord.*,
odtl_goods_id, odtl_goods_type, odtl_quantity,
con_company_name,
mer_company,
IFNULL(goods1.goodsName,goods2.goodsName) AS goodsName, IFNULL(goods1.goodsPicture,goods2.goodsPicture) AS goodsPicture
FROM `order` as ord 
LEFT JOIN order_detail as odtl on odtl_order_id=ord.order_id
LEFT JOIN consumer as con on con.con_id=ord.order_con_id 
LEFT JOIN merchant as mer on mer.mer_id=ord.order_mer_id
LEFT JOIN (SELECT 1 AS goodsType, sh_id AS goodsId, sh_picture AS goodsPicture, sh_name AS goodsName FROM server_hosting) AS goods1 ON goods1.goodsType=odtl.odtl_goods_type AND goods1.goodsId=odtl_goods_id
LEFT JOIN (SELECT 2 AS goodsType, sr_id AS goodsId, sr_picture AS goodsPicture, sr_name AS goodsName FROM server_rental) AS goods2 ON goods2.goodsType=odtl.odtl_goods_type AND goods2.goodsId=odtl_goods_id   
where order_id=1

参考
http://bbs.csdn.net/topics/390847097
https://zhidao.baidu.com/question/1367983012663347299.html
转载请标注：http://blog.csdn.net/kh717586350
首先说下思路吧，网上找了，大概两种方法：
1.在left join 的后面将两张表union all连接为一张表，并加上“区分表关键字”，然后根据“区分表关键字”筛选我们需要的数据
2.使用两个left join, 每个left join 跟上一张表，并加上“区分表关键字”，然后在 on 条件后使用“区分表关键字”
重点
left join 后面只能跟一张表，不能使用if，when case 啥的
添加“区分表关键字”进行条件判断

下面是SQL语句
第一种：一个left join 将两张表union all

SELECT 
ord.*,
odtl_goods_id, odtl_goods_type, odtl_quantity,
con_company_name,
mer_company,
goods.goodsName, goods.goodsPicture
FROM `order` as ord 
LEFT JOIN order_detail as odtl on odtl_order_id=ord.order_id
LEFT JOIN consumer as con on con.con_id=ord.order_con_id 
LEFT JOIN merchant as mer on mer.mer_id=ord.order_mer_id
LEFT JOIN (
    SELECT 1 AS goodsType, sh_id AS goodsId, sh_picture AS goodsPicture, sh_name AS goodsName FROM server_hosting
    UNION ALL
    SELECT 2 AS goodsType, sr_id AS goodsId, sr_picture AS goodsPicture, sr_name AS goodsName FROM server_rental
) AS goods ON goods.goodsType=odtl.odtl_goods_type AND goods.goodsId=odtl_goods_id
sr.sr_id=odtl.odtl_goods_id)
WHERE ord.order_id=1

第一种：使用两个left join

SELECT 
ord.*,
odtl_goods_id, odtl_goods_type, odtl_quantity,
con_company_name,
mer_company,
IFNULL(goods1.goodsName,goods2.goodsName) AS goodsName, IFNULL(goods1.goodsPicture,goods2.goodsPicture) AS goodsPicture
FROM `order` as ord 
LEFT JOIN order_detail as odtl on odtl_order_id=ord.order_id
LEFT JOIN consumer as con on con.con_id=ord.order_con_id 
LEFT JOIN merchant as mer on mer.mer_id=ord.order_mer_id
LEFT JOIN (SELECT 1 AS goodsType, sh_id AS goodsId, sh_picture AS goodsPicture, sh_name AS goodsName FROM server_hosting) AS goods1 ON goods1.goodsType=odtl.odtl_goods_type AND goods1.goodsId=odtl_goods_id
LEFT JOIN (SELECT 2 AS goodsType, sr_id AS goodsId, sr_picture AS goodsPicture, sr_name AS goodsName FROM server_rental) AS goods2 ON goods2.goodsType=odtl.odtl_goods_type AND goods2.goodsId=odtl_goods_id   
where order_id=1

参考
http://bbs.csdn.net/topics/390847097
https://zhidao.baidu.com/question/1367983012663347299.html
转载请标注：http://blog.csdn.net/kh717586350