CODEWITHSUNDEEP
ruby-on-railspostgresql
Romuloux
Romuloux

Reputation: 1183

Postgresql INNER JOIN with multiple conditons

I am working on a query that is driving me nuts. I am trying to search a table by the values in a join table. Currently, the query is returning all results that have either of the specified ids. I would only like it to return records that only have both of the ids.

Here is my table setup

creat_table champions do |t|
  t.integer :id
end

create_table champions_champion_combinations do |t|
  t.integer :champion_id
  t.integer :champion_combination_id
end

create_table champion_combinations do |t|
  t.integer :id
end

Here is the query as I have it

SELECT 
  champion_combinations.* 
FROM champion_combinations
INNER JOIN champions_champion_combinations 
  ON champions_champion_combinations.champion_combination_id = champion_combinations.id
INNER JOIN champions
  ON champions.id = champions_champion_combinations.champion_id 
WHERE champions.id IN (1, 2)"

Generated from the RoR ActiveRecord query

ChampionCombination.joins(:champions).where(champions: {id:[1,2]})

So this returns all champion_combinations that have either champion ids 1 or 2 joined to it. What type of query do I need to write that only returns the combination with both ids 1 and 2 joined to it?

Thanks in advance.

Upvotes: 0

Views: 301

Answers (2)

alexpods
alexpods

Reputation: 48485

If you're interesting in pure SQL solution, then you can use GROUP BY and HAVING clauses to achieve your goal. Here is the sql query:

SELECT cc.*
FROM champion_combinations AS cc
INNER JOIN champions_champion_combinations  AS ccc ON ccc.champion_combination_id = cc.id
WHERE ccc.champion_id IN (1, 2)
GROUP BY cc.id
HAVING array_agg(ccc.champion_id) @> ARRAY[1,2];

PS Thanks to @IgorRomanchenko for great suggestions.

Upvotes: 2

Sumit Mahamuni
Sumit Mahamuni

Reputation: 302

It is not inner join problem. Inner join is working as expected. SQL query given above doing inner join of both the tables with "champion_combination", there is no restriction which says both ids has to be present. You should do is

ChampionCombination.joins(:champions).where(champions: {id:[1,2]}).where("champion_id is not null and compion_combination_id is not null")

Upvotes: 1

Related Questions