Multiple conditions in INNER JOIN

Question

I have two tables user and product which have a one-to-many relationship (one usermay have multiple products).

I want to create a query to get all users which have both an orange and a banana. In my example below this would be john and leeroy.

How can I formulate my query to do that?

With only one condition I would go like:

SELECT * FROM "user" 
INNER JOIN "product" ON "product"."fk_user" = "user"."id"
WHERE "product"."product" = 'banana';

The user table

╔════╦═════════╗
║ id ║ name    ║
╠════╬═════════╣
║ 1  ║ michael ║
╠════╬═════════╣
║ 2  ║ john    ║
╠════╬═════════╣
║ 3  ║ leeroy  ║
╠════╬═════════╣
║ 4  ║ tony    ║
╚════╩═════════╝

The product table

╔═════════╦═════════╗
║ product ║ fk_user ║
╠═════════╬═════════╣
║ orange  ║ 1       ║
╠═════════╬═════════╣
║ orange  ║ 2       ║
╠═════════╬═════════╣
║ banana  ║ 2       ║
╠═════════╬═════════╣
║ banana  ║ 3       ║
╠═════════╬═════════╣
║ orange  ║ 3       ║
╠═════════╬═════════╣
║ banana  ║ 4       ║
╚═════════╩═════════╝

You don't need a join. See my answer on this. This isn't a product table by the way; because a record in the table doesn't uniquely represent a product. You should ideally have a product table with one row per product and a user_product table linking product and user. — Thorsten Kettner, Jan 09 '19 at 21:12
Do you just need `user_id` or more columns from the `user` table? Is there a `FOREIGN KEY` constraint to enforce referential integrity? A `UNIQUE` constraint to disallow dupes on `product(product, fk_user)`? Your version of Postgres? — Erwin Brandstetter, Jan 09 '19 at 22:43

dfundako · Accepted Answer · 2019-01-09T21:16:32.787

5

You could use two joins:

SELECT u.* 
FROM user u
INNER JOIN product p1
ON p1.fk_user=u.id
AND p1.product='banana'
INNER JOIN product p2
ON p2.fk_user=u.id
AND p2.product='orange'

edited Jan 09 '19 at 21:16

answered Jan 09 '19 at 21:04

dfundako

8,022
3
18
34

One banana is supposed to be an orange I guess. ;) – sticky bit Jan 09 '19 at 21:16
@stickybit derp duh derp derp. Thanks – dfundako Jan 09 '19 at 21:16

Thorsten Kettner · Answer 2 · 2019-01-09T21:19:57.410

Group by user und use HAVING to check the user's products.

select *
from user
where id in
(
  select fk_user
  from product
  group by fk_user
  having count(case when product = 'orange' then 1 end) > 0
     and count(case when product = 'banana' then 1 end) > 0
);

Edit: I should add that there are several ways to write such subquery. A WHERE clause could speed this up, and with such clause you could just count distinct products found:

select *
from user
where id in
(
  select fk_user 
  from product 
  where product in ('orange', 'banana') 
  group by fk_user
  having count(distinct product) = 2 -- two different products: orange and banana
);

D Stanley · Answer 3 · 2019-01-09T23:08:31.560

3

The most straightforward declaration (IMHO) would be to use two IN clauses:

select *
from user
where id in
(
  select fk_user
  from product
  WHERE product = 'orange')
and id in
(
  select fk_user
  from product
  WHERE product = 'banana')

edited Jan 09 '19 at 23:08

answered Jan 09 '19 at 21:21

D Stanley

149,601
11
178
240

1

Canonical may not be the right word - my intent is to say "the most straightforward _declarative_" way. It's the closest SQL that expresses the _intent_ of the query (`EXISTS` is a close second IMHO) – D Stanley Jan 09 '19 at 23:05
Fair enough. (`EXISTS` would win that competition from where I stand - and it can be faster while it's never slower.) – Erwin Brandstetter Jan 09 '19 at 23:13

score 1 · Answer 4 · answered Jan 09 '19 at 21:48

If you just want the user id and not the name, you can use aggregation:

SELECT p.fk_user
FROM product p
WHERE p.product in ('banana', 'orange')
GROUP BY p.fk_user
HAVING COUNT(*) FILTER (WHERE p.product = 'banana') > 0 AND
       COUNT(*) FILTER (WHERE p.product = 'orange') > 0;

If you need additional columns from user as well, I would go with the IN version suggested by DStanley, although I would use EXISTS rather than IN.

Erwin Brandstetter · Answer 5 · 2019-01-09T23:03:58.220

This is a case of relational-division.
If (like is the typical case, and your sample data seems to support it) ...

all you need is the user ID
there is a FOREIGN KEY constraint enforcing referential integrity
and a UNIQUE constraint on product(product, fk_user) - (implicitly providing the perfect index for my query)

.. then this is as fast as it gets:

SELECT fk_user
FROM   product b
JOIN   product o USING (fk_user)
WHERE  b.product = 'banana'
AND    o.product = 'orange';

We collected an arsenal of base techniques here:

How to filter SQL results in a has-many-through relation

The best pick depends on missing specifications - and personal preferences to a degree.

Aside: user is a resserved word, don't use it as table name.

Multiple conditions in INNER JOIN

5 Answers5