ID Name
1 P
2 N
3 N
From the table detail:
ID OrderID ItemId
1 1 1
2 1 2
2, 3, 3
4, 3, 4,
4 3 1
Item table
ID ItemName Status
Apple 1
2 Tomato 1
3 Strawberry 0
4 the Pear 0
Problem description
Said the item is listed in the table the Status Status, an order (order) with multiple detailed content (detail), if an order in the detailed list an item in the Status of 0, the order cannot be found, according to the above data order correct results for
ID Name
1 P
Because the detail list of the Item number 2 the status of 0
Number 3 for the detail list of Numbers for the status of Item 4 to 0, so can't find out
CodePudding user response:
SELECT *FROM the ORDER D
WHERE NOT EXISTS
(SELECT one FROM the ORDER A
JOIN the DETAIL ON Anderson, D=B B.O RDERID
JOIN the ITEM C ON B.I TEMID=C.I D
WHERE the STATUS=0
AND D.I D=Anderson, D)