加入两个有效的SQL查询会得到错误的结果-我在哪里出错 [英] Joining two working sql queries gets wrong results - Where am i going wrong

问题描述

我有两个分别返回正确数据的查询，但是当我尝试将它们联接时，返回的数据是错误的. 查询之一是:

I have two queries that individually return the correct data, but when I tried to join them the returned data is wrong. Query one is:

SELECT op.orders_id, op.products_id, op.products_name, op.final_price, op.products_quantity
FROM orders_products op
WHERE op.orders_id = 21535

查询二是:

SELECT opa.products_options_values_id, opa.products_prid
FROM orders_products_attributes opa
WHERE opa.orders_id = 21535

我尝试过的组合查询是:

The combined query I tried was:

SELECT op.orders_id, op.products_id, op.products_model, op. products_name, op.final_price, op.products_quantity, opa.products_options_values_id, opa.products_prid
FROM orders_products op
JOIN orders_products_attributes opa
ON op.orders_id = opa.orders_id
WHERE op.orders_id = 21535

提琴在这里: http://www.sqlfiddle.com/#!8/ef9be/5

我不知道如何解决此问题.我在opa.products_options_values_id上尝试了Group BY，但这引起了问题，因为数据结果中有两个结果40. 任何帮助表示赞赏.

I've no idea how to fix this. I tried Group BY on opa.products_options_values_id but that caused issues because two results of 40 are in the data result. Any help appreciated.

推荐答案

在您的join条件下，您使用了orders_id.您应该像这样使用orders_products_id:

In your join condition, you used orders_id. You should use orders_products_id like this:

SELECT op.orders_id, op.products_id, op.products_model, op. products_name,
       op.final_price, op.products_quantity, opa.products_options_values_id,
       opa.products_prid
FROM orders_products op
JOIN orders_products_attributes opa ON op.orders_products_id = opa.orders_products_id
WHERE op.orders_id = 21535

这篇关于加入两个有效的SQL查询会得到错误的结果-我在哪里出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！