Member Avatar for klactose

Hello,

I am somewhat of a SQL newbie. My question is how would I go about creating a query that searched for things like:
1. users who have collected a complete set of promo items
2. users who have purchased every movie from actor X
3. users who have purchased every item less than $10
etc...

Any assistance would be greatly appreciated.

  • 2 Contributors
  • 3 Replies
  • 107 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

As noted, a table layout is necessary to pick the right SQL pattern. A minimal, common schema for these problems is: USERS(user_id), ITEMS(item_id, actor_id, price, promo_id), and PURCHASES(user_id, item_id, qty, purchased_on). The three goals in 's post are all examples of "relational division" — find users who have every member of some target set. The usual, reliable patterns are (1) GROUP BY + HAVING, (2) double-NOT-EXISTS (anti-join), and (3) Oracle-specific MINUS.

Example (HAVING + COUNT DISTINCT — good when comparing counts):

SELECT p.user_id
FROM purchases p
JOIN items i ON p.item_id = i.item_id
WHERE i.promo_id = 'PROMO1'
GROUP BY p.user_id
HAVING COUNT(DISTINCT p.item_id) =
  (SELECT COUNT(*) FROM items WHERE promo_id = 'PROMO1');

Example (NOT EXISTS — clearer semantically, often efficient):

SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM items i
  WHERE i.promo_id = 'PROMO1'
    AND NOT EXISTS (
      SELECT 1 FROM purchases p
      WHERE p.user_id = u.user_id AND p.item_id = i.item_id
    )
);

Example (Oracle MINUS — compact set-difference test):

SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  (SELECT item_id FROM items WHERE promo_id = 'PROMO1')
  MINUS
  (SELECT item_id FROM purchases WHERE user_id = u.user_id)
);

Notes and cautions: use COUNT(DISTINCT ...) when multiple purchases of the same item exist; if the target set can be empty, add a guard (empty target typically makes every user match). If a requirement includes quantities (e.g., must buy 3 copies), replace counts with SUM(p.qty) and compare to the required number. For performance, ensure an index on purchases(user_id, item_id) and selective indexes on items (promo_id, actor_id, price). Posting actual DDL and sample rows (as requested) allows concrete tuning and plan checks with EXPLAIN PLAN.

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

do not expect us to guess and solve your problem.

Please post the structure of your tables.

Member Avatar for klactose

Oh my apologies for not being clear with what I was curious about. I was asking in a more generic sense, simply to try to understand some of SQL's syntax. There is no actual database as of yet.

However, I am discovering that it is difficult to discuss database topics without an actual database.

Member Avatar for debasisdas

you need to pass the condition in the where clause of the query as per your requirement.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.