Selecting rows that belong to two others using joins table

Question

Given two models Player and Team how do I select all teams that two players specific players belong to?

Example Rails models and db table setup:

Rails models:

class Player < ActiveRecord::Base
  has_and_belongs_to_many :teams, uniq: true
end
class Team < ActiveRecord::Base
  has_and_belongs_to_many :players, uniq: true
end

SQL Tables

    teams         players            players_teams
+----------------------------------------------------+
| id | name |   | id | name |  | player_id | team_id |
+----+------+   +----+------+  +-----------+---------+
|  1 | Foo  |   |  1 | Jack |  |      1    |    1    |
|  2 | Bar  |   |  2 | Jill |  |      1    |    2    |
|    |      |   |    |      |  |      2    |    1    |

How do I select all teams that have both Jack and Jill on them? (In this case, just team Foo.)

This is a typical case of relational division. We assembled a whole arsenal of query techniques [under this related question.](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879) — Erwin Brandstetter, Mar 08 '14 at 00:27
Ah, thanks Erwin. My question is actually an exact duplicate of that one. I didn't see that one earlier. — Andrew Hubbs, Mar 08 '14 at 00:29
Once you know the term ("relational division"), it's easier to search. — Erwin Brandstetter, Mar 08 '14 at 00:30
Yeah, I spent a while trying to decide how to even word what I was talking about. It would appear relational division was the exact thing I was searching for. Thanks again. — Andrew Hubbs, Mar 08 '14 at 00:31

Rafa Paez · Answer 1 · 2014-03-08T00:15:32.273

2

This is a very tricky question! It is not just an OR/AND in a where clause. Here you are a possible solution in a single SQL query using GROUP BY and MAX

SELECT t.*
  FROM teams t 
  JOIN players_teams pt ON t.id = pt.team_id
  JOIN players p ON p.id = pt.player_id
WHERE p.name IN ('Jack', 'Jill')
GROUP BY t.id
HAVING MAX(p.name = 'Jack') = 1 AND MAX(p.name = 'Jill') = 1

SQL Fiddle

In ActiveRecord should be something like this (untested)

Team.joins(:players).where("players.name IN (?)", ["Jack", "Jill"])
.group("teams.id")
.having("MAX(players.name = ?) = 1 AND MAX(players.name = ?) = 1", "Jack", "Jill")

edited Mar 08 '14 at 00:15

answered Mar 07 '14 at 23:23

Rafa Paez

4,820
18
35

Thanks. I knew this was straightforward and that I was just blanking on the basics on a Friday afternoon. – Andrew Hubbs Mar 07 '14 at 23:38
@Rafa Can you share the SQL equivalent of this. – Kirti Thorat Mar 07 '14 at 23:40
2

Doesn't this query return both "Foo" and "Bar", not just "Foo"? – Carlos Ramirez III Mar 07 '14 at 23:41
Ah, actually I think this is going to be wrong. Because the above is going to return all teams that have either player, rather than only the teams that have both players. – Andrew Hubbs Mar 07 '14 at 23:41
@carlosramireziii +1 Thats what I thought – Kirti Thorat Mar 07 '14 at 23:42
Thanks for the help Rafa. If you are still interested a great answer to a duplicate question exists here. http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879 – Andrew Hubbs Mar 08 '14 at 00:34
@Rafa +1 I think this would work now. – Kirti Thorat Mar 08 '14 at 01:59

Carlos Ramirez III · Answer 2 · 2014-03-08T00:00:58.923

1

You can get the intersection of two separate queries like this

Team.includes(:players).where(players: { name: 'Jack' }) & Team.includes(:players).where(players: { name: 'Jill' })

For a single query, try the following

Team.includes(:players).where(players: { name: ['Jack', 'Jill'] }).group('teams.id').having('COUNT(players.id) = 2')

edited Mar 08 '14 at 00:00

answered Mar 07 '14 at 23:44

Carlos Ramirez III

7,314
26
33

Thanks Carlos. That will definitely work. I would like to find a way to do it without using two queries though. – Andrew Hubbs Mar 07 '14 at 23:44
Great Job!! I had a doubt thats why I asked @Rafa to share the SQL query. – Kirti Thorat Mar 07 '14 at 23:46
I just put the SQL query and a SQL Fiddler. Please check my updated answer. – Rafa Paez Mar 08 '14 at 00:16
Thanks for the help Carlos. If you are still interested a great answer to a duplicate question exists here. http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879 – Andrew Hubbs Mar 08 '14 at 00:34
Happy to help. Thanks for sharing the link. This was an interesting problem, so I'm curious to see the answer. – Carlos Ramirez III Mar 08 '14 at 02:41

score 0 · Answer 3 · answered Mar 07 '14 at 23:25

0

Teams.all(:include => :player, conditions => ["player.name in (?)", ["Jack","Jill"]])

answered Mar 07 '14 at 23:25

slapthelownote

4,249
1
23
27

Andrew Hubbs · Answer 4 · 2014-03-08T00:12:35.843

0

This is the solution I came up with that uses a subquery. Does anyone have a cleaner more AREL way of accomplishing this?

SELECT te1.* FROM (
  SELECT DISTINCT "teams".* FROM "teams"
  INNER JOIN "players_teams" ON "teams"."id" = "players_teams"."team_id"
  WHERE "players_teams"."player_id" = 2
) te1 INNER JOIN "players_teams" ON "te1"."id" = "players_teams"."team_id"
  WHERE "players_teams"."player_id" = 1;

edited Mar 08 '14 at 00:12

answered Mar 07 '14 at 23:58

Andrew Hubbs

9,338
9
48
71

You can check out my updated answer above. – Carlos Ramirez III Mar 08 '14 at 00:13

Selecting rows that belong to two others using joins table

4 Answers4