CODEWITHSUNDEEP
phpmysqlsql
eldiablo62
eldiablo62

Reputation: 317

Select the better result match

I have a little problem for have the good solution. I need to search the better result.

I have 3 tables, rule, meaning, and rule_meaning. 1 rule can have 1 or more meaning.

This is the result for 

SELECT * FROM rule, rule_meaning, meaning WHERE rule_meaning.rule_id = rule.id AND rule_meaning.meaning_id = meaning.i

rule.id rule.name   rule.id meaning_id  meaning.id  meaning.name
1   RADIO - Ecouter   1        1          1          ACTIVER
1   RADIO - Ecouter   1        6          6          RADIO
2   LUMIERE Allumer   2        1          1          ACTIVER
3   RADIO + volume    3        6          6          RADIO
4   RADIO - volume    4        6          6          RADIO

When i search ACTIVER and RADIO i want to have only RADIO - Ecouter (rule.id = 1)

I have tried

Upvotes: 2

Views: 51

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to select rules that have both meanings ACTIVER and RADIO. You can do this with an aggregation:

select *
from rule
where id in
(
  select rule_id
  from rule_meaning 
  where meaning_id in (select id from meaning where name in ('ACTIVER', 'RADIO'))
  group by rule_id
  having count(*) = 2 -- both meanings
);

Another approach I like less would be to look up each value separately with EXISTS or IN. I am showing it here because it's the simple way how to approach the task.

select *
from rule
where id in
(
  select rule_id
  from rule_meaning 
  where meaning_id = (select id from meaning where name = 'ACTIVER')
)
and id in
(
  select rule_id
  from rule_meaning 
  where meaning_id = (select id from meaning where name = 'RADIO')
);

Upvotes: 2

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

You may want to use a case statement e.g

    CASE WHEN WHERE meaning.name = ACTIVER AND meaning.name = RADIO
    THEN ....
    ELSE

But i think what you're asking is 

    WHERE meaning.name = ACTIVER AND rule.name = RADIO 
    AND rule.id = 1

Upvotes: 0

Related Questions