Search For Orders That Have Two Products, One With Specific Reference, Other With Specific Description

I have a SQL query issue that seems easy to fix but I can't figure out how to make it work.. I basically have two tables : Orders, and OrderDetails... Each order have several produ

Solution 1:

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"
)

Solution 2:

Your query isn't matching your data. There are no records that match d.reference = "F40" AND d.description = "Epee".

If you want to return order number QQ00000QQ then you need

SELECT
  o.orderNumber

FROM
  `order` AS o

JOIN
  `orderDetail` AS d ON o.id = d.orderID

WHERE
  d.reference = "F40" AND 
  d.description = "Wire" //Note change to condition

Solution 3:

I think you must a create an field same. example in : order = id_order orderDetail = id_order

Then if you want to find all, you must insert that id_order same. example :

INSERT INTO `order`
(id_order , orderNumber)
VALUES
('1','QQ00000QQ'),
('2','AA11111AA'),
('3','LO00000OL'),
('4','AA12345BB');


INSERT INTO `orderDetail`
(orderID, reference, description,id_order)
VALUES
(1, 'F40', 'Wire','1'),
(1, 'Q25', 'Epee','1'),
(1, 'Z99', 'Mask','1'),
(2, 'F40', 'Wire','2'),
(3, 'Q25', 'Epee','2'),
(4, 'F40', 'Wire','4'),
(4, 'Z99', 'Mask','3');

SELECT
  o.orderNumber

FROM
  `order` AS o

JOIN
  `orderDetail` AS d ON o.id = d.orderID

WHERE
  d.reference = '4'

GROUP BY
  o.id