I have three columns in SQL and I am trying to get all columns that match

Question

Desired Result: Find everyone who has purchased both gum and baskets.

Example: (However, just about every column is stored on different tables.)

Example

Please declare your RDBMS and version, provide table definition(s) (`CREATE TABLE` statement(s)), and sample values as *text*, never as image. — Erwin Brandstetter, Aug 21 '20 at 02:50
I couldn't make sense of this: `(However, just about every column is stored on different tables.)` - nor the title, for that matter. — Erwin Brandstetter, Aug 21 '20 at 02:53
Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , Aug 21 '20 at 05:49

Erwin Brandstetter · Answer 1 · 2020-08-21T05:51:17.033

0

Desired Result: Find everyone who has purchased both gum and baskets.

This is a typical case of relational-division. Assuming a UNIQUE (or PK) constraint on (name, purchase) a query like this does the job:

SELECT name
FROM   tbl t1
JOIN   tbl t2 USING (name)
WHERE  t1.purchase = 'Gum'
AND    t2.purchase = 'Baskets';

See:

edited Aug 21 '20 at 05:51

answered Aug 21 '20 at 02:48

All of the purchases are on the same table in the same column. That's what makes it so hard. – Skinnyfrog333 Aug 21 '20 at 17:29
@Skinny That's also what makes it relational division. (I am using a single table.) – Erwin Brandstetter Aug 22 '20 at 16:57

1 Answers1