搜索具有两种产品的订单，一种具有特定的参考，另一种具有特定的描述 [英] Search for orders that have two products, one with specific reference, other with specific description

问题描述

我有一个似乎很容易解决的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"
)

这篇关于搜索具有两种产品的订单，一种具有特定的参考，另一种具有特定的描述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！