问题描述
我有一个似乎很容易解决的SQL查询问题,但我不知道如何使它工作..
I have a SQL query issue that seems easy to fix but I can't figure out how to make it work..
我基本上有两个表:Orders和OrderDetails ...每个订单都有几个产品,已在OrderDetails表中注册.我希望能够找到所有有2种产品的订单;一个带有特定的参考,另一个带有特定的说明.
I basically have two tables : Orders, and OrderDetails... Each order have several products, registered in OrderDetails table.I want to be able to find all the orders that have 2 products ; one with a specific reference, and the other with a specific description.
这是我写的查询:
SELECT
o.orderNumber
FROM
`order` AS o
JOIN
`orderDetail` AS d ON o.id = d.orderID
WHERE
d.reference = "F40" AND
d.description = "Epee"
这是小提琴: http://sqlfiddle.com/#!2/bd94e/1
查询返回0记录,并且应返回订单号QQ00000QQ
The query is returning 0 reccord, and it should return order number QQ00000QQ
有人可以向我解释如何使该查询有效吗?非常感谢!!
Can someone please explain to me how can I make that query work?? Thank you very much!!
推荐答案
如果我对您的理解正确,那么您想查找一个订单,其中一个订单行满足条件(引用="F40"),而另一个订单行满足另一条件( description ="Epee").
If I understand you correctly, you want to find an order that has one orderline satisfying a condition (reference = "F40") and another orderline satisfying another condition (description = "Epee").
进行单个联接将无法解决此问题,因为您将搜索一条同时满足这两个条件的订单行.您应该改为执行以下操作:
Doing a single join will not solve this, as you will be searching for one orderline that satisfies both conditions. You should do something like this instead:
SELECT orderNumber FROM `order`
WHERE id IN (
SELECT orderid FROM orderDetail od1
INNER JOIN orderDetail od2
USING (orderid)
WHERE od1.reference = 'F40' AND od2.description = "Epee"
)
这篇关于搜索具有两种产品的订单,一种具有特定的参考,另一种具有特定的描述的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!