使用mysql中的内部和外部联接从3个表中选择记录 [英] Select record from 3 tables using inner and outer join in mysql

问题描述

我想从`oc_product，oc_product_description，oc_product_special`中选择记录`product_id product_name actual_price discount_price`。 `oc_product_special` 表包含产品的折扣价，但在某些产品上没有折扣，即discount_price = 0.0000。我想用discount_price选择所有40个产品，如果discount_price = 0.0000则在该行中打印NULL。



我试过的查询是：

`SELECT oc_product_description.product_id，oc_product_description.name product_name，oc_product.price actual_price，oc_product_special.price discount_price  FROM  oc_product  INNER   JOIN  oc_product_description  RIGHT   OUTER   JOIN  oc_product_special  ON  oc_product_description.product_id = oc_product.product_id = oc_product_special.product_id` 

它返回错误的输出，如

`product_id product_name actual_price discount_price 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 0.0000 
 NULL NULL NULL 1950.0000 
 NULL NULL NULL 3850.0000 
 NULL NULL NULL 7500.0000` 

我希望这个输出：

`product_id product_name actual_price discount_price 
 1 yyyy 1000.0000 0.0000 
 2 xxxx 2000.0000 500.0000` 

解决方案

似乎你的查询错了。



它应该是这样的：

  SELECT  oc_product_description.product_id，oc_product_ description.name product_name，oc_product.price actual_price，oc_product_special.price discount_price 
  FROM  oc_product 
  INNER   JOIN  oc_product_description  ON  oc_product_description.product_id = oc_product.product_id 
  RIGHT   OUTER   JOIN  oc_product_special  ON  oc_product.product_id = oc_product_special.product_id 

如需了解更多信息，请参阅： 13.2.9.2 JOIN语法 [ ^ ]



我建议使用表别名 [ ^ ]使查询更加清晰。

您好，



我同意 [ Maciej Los ^ ]。



除了解决方案还想添加一件东西。



根据我对你的了解查询和要求，oc_product_special表包含折扣信息。（如果适用）。因此，oc_product和oc_product_special表之间应该是左外连接。因为在您的查询结果中左侧表格更重要。所以，你不会得到带有NULL值的product_Id。



请在下面查询：



< pre lang =sql> SELECT oc_product_description.product_id，oc_product_description.name product_name，oc_product.price actual_price，oc_product_special.price discount_price
FROM oc_product
INNER JOIN oc_product_description ON oc_product_description.product_id = oc_product.product_id
LEFT OUTER JOIN oc_product_special ON oc_product.product_id = oc_product_special.product_id

如果您对此有任何疑问或疑问，请与我们联系。



谢谢

I want to select records `product_id product_name actual_price discount_price` from `oc_product, oc_product_description, oc_product_special`. `oc_product_special` table contains discount price of product but on some products there is no discount i.e. discount_price=0.0000. I want to select all 40 products with their discount_price and if discount_price=0.0000 then print NULL in that row.

Query which I tried is:

`SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price FROM oc_product INNER JOIN oc_product_description RIGHT OUTER JOIN oc_product_special ON oc_product_description.product_id = oc_product.product_id = oc_product_special.product_id`

it returns me wrong output like

`product_id 	product_name 	actual_price 	discount_price
  NULL 	          NULL 	           NULL 	       0.0000
  NULL            NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       1950.0000
  NULL 	          NULL 	           NULL 	       3850.0000
  NULL 	          NULL 	           NULL 	       7500.0000`

And I expect this output:

`product_id 	product_name 	actual_price 	discount_price
  1               yyyy 	           1000.0000 	  0.0000
  2               xxxx 	           2000.0000 	  500.0000`

解决方案

Seems your query is wrong.

It should be something like:

SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price
FROM oc_product
    INNER JOIN oc_product_description ON oc_product_description.product_id = oc_product.product_id 
    RIGHT OUTER JOIN oc_product_special ON oc_product.product_id = oc_product_special.product_id

For further information, please see: 13.2.9.2 JOIN Syntax[^]

I'd suggest to use table aliases[^] to make query more clear.

Hello,

I agreed with the solution by [Maciej Los^].

In addition to the solution want to add one more thing.

As per my understanding of your query and requirement, "oc_product_special" table contains discount information.(If applicable). So, it should be Left outer join between oc_product and oc_product_special tables. Because in your query result of left side tables is more important. so, u will not get the product_Id with NULL values.

Please try below query:

SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price
FROM oc_product
    INNER JOIN oc_product_description ON oc_product_description.product_id = oc_product.product_id
    LEFT OUTER JOIN oc_product_special ON oc_product.product_id = oc_product_special.product_id

Please let me know if you have any concern or query on this.

Thanks

这篇关于使用mysql中的内部和外部联接从3个表中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！