如何使用case条件连接多个表 [英] How to join multiple table with case conditional

问题描述

我有表名order_booking。它有一个字段名称是'type'，类型的值类似'商业，包装，订单'。



我有另外三个表'commercial_estimation'，'packaging_estimation'和'order_estimation'。



我的问题是我想用这三个表加入'order_booking'表，但条件意味着我有一个'商业'类型然后加入使用'commercial_estimation'表，如果我有'packaging'类型，那么加入'packaging_estimation'表，如果我有'Order'类型，那么加入'order_estimation'表。所以，我只能加入任何一个基于'类型'的表。



我尝试了什么：

I have table name "order_booking". It has one field name is 'type' and value of type like 'commercial,packaging,order'.

I have another three table 'commercial_estimation','packaging_estimation' and 'order_estimation'.

My question is that i want to join 'order_booking' table with this three table but conditional means if i have a type 'commercial' then join with 'commercial_estimation' table,if i have type 'packaging' then join with 'packaging_estimation' table and if i have type 'Order' then join with 'order_estimation' table. So, i can only join with any one table based on 'type'.

What I have tried:

SELECT a.type FROM  order_booking a 
    (Case when type a.type = 'commercial' Then 'Inner Join commercial_estimation  b on a.id = b.oid' 
          when type a.type = 'packaging' Then 'Inner Join packaging_estimation  b on a.id = b.oid' 
          else  type a.type = 'order' Then 'Inner Join order_estimation  b on a.id = b.oid' END) 

推荐答案

这是怎么回事：

How is about this:

SELECT 
  a.type,
   
FROM  order_booking a
LEFT JOIN commercial_estimation b ON a.type = 'commercial' AND b.oid = a.id
LEFT JOIN packaging_estimation c ON a.type = 'packaging' AND c.oid = a.id
LEFT JOIN order_estimation d ON a.type = 'order' AND d.oid = a.id

要访问详细信息，您还可以使用 COALESCE ，以防数据类型匹配并且您希望拥有一个字段三个细节。

To have access to the Details you can also use COALESCE in case the datatype do match and you like to have one field for the three Details.

这篇关于如何使用case条件连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！