CODEWITHSUNDEEP
mysqlsql
Pioz
Pioz

Reputation: 6321

Select records with exactly the same many to many associations

I have 2 tables: rules and measure_units. A rule has many measure_units and a measure_unit has_many rules. So we have a join table with rule_id and measure_unit_id.

Now, given a rule I want to select all the rules with exactly the same measure units.

rule1 => grams
rule2 => grams,meters
rule3 => grams,meters,litre
rule4 => meters
rule5 => grams,meters
rule6 => litre,grams

Given the rule2 (with grams and meters) I need to select the rules 2 and 5.

My try was:

SELECT `rules`.* FROM `rules`
LEFT JOIN `measurables` ON `measurables`.`rule_id` = `rules`.`id`
LEFT JOIN `measure_units` ON `measure_units`.`id` = `measurables`.`measure_unit_id`
WHERE `measure_units`.`id` IN (1,2)
GROUP BY `measurables`.`rule_id`
HAVING (count(*) = 2)

But this query does not work beacause select all rules that has at least the measure units with id 1 and 2. So also the rule 3.

Possible a query compatible with MySQL.

Here a db-fiddle: https://www.db-fiddle.com/f/vxg6vYjpSYJqN7sn83JLei/1 where to try the query.

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I would extract the units for rule 2 and compare that to all other rules. MySQL makes this easy using group_concat:

select r.*
from (select m.rule_id,
             group_concat(m.measure_unit_id order by measure_unit_id) as measure_unit_ids
      from measurables m
      group by m.rule_id
     ) r join
     (select group_concat(m.measure_unit_id order by measure_unit_id) as measure_unit_ids
      from measurables m
      where m.rule_id = 2
     ) r2
     on r.measure_unit_ids = r2.measure_unit_ids

If you need more information about the rules, you can join in the rules table.

Upvotes: 1

Related Questions