Korzystając z tabeli Order_Items znajdź te zamówienia, które zawierają produkty o id 3127 i 3106 (oba jednocześnie). Zadanie rozwiąż na co najmniej dwa sposoby.
Schemat: OE, Tabela: Order_Items, Kolumny: order_id, product_id, Wynik: 13 rows
[spoiler title=’Rozwiązanie’ style=’default’ collapse_link=’true’]
- Z użyciem INTERSECT:
SELECT order_id
FROM order_items
WHERE product_id = 3127
INTERSECT
SELECT order_id
FROM order_items
WHERE product_id = 3106
ORDER BY order_id;
- Z użyciem EXISTS:
SELECT order_id
FROM order_items oi
WHERE product_id = 3127
AND EXISTS (
SELECT order_id
FROM order_items oi2
WHERE product_id = 3106 AND oi.order_id = oi2.order_id
)
ORDER BY order_id;
- Z użyciem IN:
SELECT order_id
FROM order_items
WHERE product_id = 3127
AND order_id IN
( SELECT order_id FROM order_items WHERE product_id = 3106);
- Z użyciem klauzuli WITH:
WITH Subquery AS (
SELECT order_id
FROM order_items
WHERE product_id = 3127 OR product_id = 3106
)
SELECT *
FROM Subquery
GROUP BY order_id
HAVING COUNT(*) >1
ORDER BY order_id;
- Z podzapytaniem w klauzuli FROM:
SELECT order_id
FROM (
SELECT order_id
FROM order_items
WHERE product_id = 3127 OR product_id = 3106
) Tab
GROUP BY order_id
HAVING COUNT(*) >1
ORDER BY order_id;
[/spoiler]