By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH order_details AS (
SELECT 9 AS order_id, 7 AS product_id UNION ALL
SELECT 10, 5 UNION ALL
SELECT 10, 6 UNION ALL
SELECT 11, 6 UNION ALL
SELECT 11, 7
),
products AS (
SELECT 5 AS product_id, 'potato' AS product_name, 4.99 AS price UNION ALL
SELECT 6, 'potato *', 7.5 UNION ALL
SELECT 7, 'orange', 7.99
)
SELECT
od.order_id
FROM order_details od
INNER JOIN products p
ON od.product_id = p.product_id
GROUP BY
od.order_id
HAVING
COUNT(DISTINCT p.product_name) <> COUNT(DISTINCT REPLACE(p.product_name, ' *', ''));
| order_id |
|---|
| 10 |