如果条件，如何在选择的staments中使用select stament(How to use select statement within select statements if condition)

我想做什么：

我想从订单表中获取带有它的id的产品名称，但在我的情况下，我将产品存储在3个不同的表中。 即菜，饮料，其他。

问题：

所以问题在于订单表我已经放了一种类型（它是什么样的产品），如“dirnks，dish，others”。所以当产品可以放在任何桌子（菜，饮料）时，我怎样才能得到产品的名称，其他）

我想要的是：

我不知道是否可能但是如果有人能告诉我们下面的查询是什么，我如何在IF语句中使用select语句

SELECT product_id,type,quantity , ( IF(type='Drinks) THEN (SELECT drink_name FROM drinks WHERE drink_id=product_id) ELSE IF (type='Dish) THEN (SELECT dish_name FROM dish WHERE dish_id=product_id) ) as drink_name FROM order_product WHERE shop_id='JSMMSK730'

请告诉我这件事是否可能，如果可能的话请给你答案

What I am trying to do:

I want to get the name of the product with it's id from orders table , but in my case I have stored products in 3 different tables. i.e dish,drinks,others.

Problem:

So the problem is in orders table i have put a type(what kind of product it is) like "dirnks,dish,others".So how can i get the name of the product when product can be in any tables(dish,drinks,others)

What i want:

I don't whether it's possible or not but if anyone can tell what below query is there,how can i use select statement in IF statment

SELECT product_id,type,quantity , ( IF(type='Drinks) THEN (SELECT drink_name FROM drinks WHERE drink_id=product_id) ELSE IF (type='Dish) THEN (SELECT dish_name FROM dish WHERE dish_id=product_id) ) as drink_name FROM order_product WHERE shop_id='JSMMSK730'

Is this thing possible or not?

最满意答案

使用UNION ，并向子查询添加一个新列，指示行来自哪个表。 然后你可以在JOIN条件下使用它。

SELECT product_id, o.type, quantity, name FROM order_product o JOIN ( SELECT "dish" type, dish_id id, drink_name name from dish UNION SELECT "drinks" type, drink_id id, drink_name name from drinks UNION SELECT "others" type, other_id id, other_name name from others ) p ON p.id = o.product_id and p.type = o.type WHERE shop_id = 'JSMMSK730'

Use a UNION, and add a new column to the subquery that indicates which table the rows came from. Then you can use this in the JOIN condition.

SELECT product_id, o.type, quantity, name FROM order_product o JOIN ( SELECT "dish" type, dish_id id, drink_name name from dish UNION SELECT "drinks" type, drink_id id, drink_name name from drinks UNION SELECT "others" type, other_id id, other_name name from others ) p ON p.id = o.product_id and p.type = o.type WHERE shop_id = 'JSMMSK730'如果条件，如何在选择的staments中使用select stament(How to use select statement within select statements if condition)

我想做什么：

我想从订单表中获取带有它的id的产品名称，但在我的情况下，我将产品存储在3个不同的表中。 即菜，饮料，其他。

问题：

所以问题在于订单表我已经放了一种类型（它是什么样的产品），如“dirnks，dish，others”。所以当产品可以放在任何桌子（菜，饮料）时，我怎样才能得到产品的名称，其他）

我想要的是：

我不知道是否可能但是如果有人能告诉我们下面的查询是什么，我如何在IF语句中使用select语句

SELECT product_id,type,quantity , ( IF(type='Drinks) THEN (SELECT drink_name FROM drinks WHERE drink_id=product_id) ELSE IF (type='Dish) THEN (SELECT dish_name FROM dish WHERE dish_id=product_id) ) as drink_name FROM order_product WHERE shop_id='JSMMSK730'

请告诉我这件事是否可能，如果可能的话请给你答案

What I am trying to do:

I want to get the name of the product with it's id from orders table , but in my case I have stored products in 3 different tables. i.e dish,drinks,others.

Problem:

So the problem is in orders table i have put a type(what kind of product it is) like "dirnks,dish,others".So how can i get the name of the product when product can be in any tables(dish,drinks,others)

What i want:

I don't whether it's possible or not but if anyone can tell what below query is there,how can i use select statement in IF statment

SELECT product_id,type,quantity , ( IF(type='Drinks) THEN (SELECT drink_name FROM drinks WHERE drink_id=product_id) ELSE IF (type='Dish) THEN (SELECT dish_name FROM dish WHERE dish_id=product_id) ) as drink_name FROM order_product WHERE shop_id='JSMMSK730'

Is this thing possible or not?

最满意答案

使用UNION ，并向子查询添加一个新列，指示行来自哪个表。 然后你可以在JOIN条件下使用它。

SELECT product_id, o.type, quantity, name FROM order_product o JOIN ( SELECT "dish" type, dish_id id, drink_name name from dish UNION SELECT "drinks" type, drink_id id, drink_name name from drinks UNION SELECT "others" type, other_id id, other_name name from others ) p ON p.id = o.product_id and p.type = o.type WHERE shop_id = 'JSMMSK730'

Use a UNION, and add a new column to the subquery that indicates which table the rows came from. Then you can use this in the JOIN condition.

SELECT product_id, o.type, quantity, name FROM order_product o JOIN ( SELECT "dish" type, dish_id id, drink_name name from dish UNION SELECT "drinks" type, drink_id id, drink_name name from drinks UNION SELECT "others" type, other_id id, other_name name from others ) p ON p.id = o.product_id and p.type = o.type WHERE shop_id = 'JSMMSK730'