CODEWITHSUNDEEP
mysqlsqlselect
Jean
Jean

Reputation: 59

Select rows having equal values in one column but not those are alone

This is my SQLfiddle : http://sqlfiddle.com/#!9/6a335/1/0

enter image description here

The result I want is the same like picture but without the 2 red rows, because for id_association_mots_idM 476 and 478, there is no traduction in english in the table.

I tried many requests and many ways. Is it possible and if yes, how ?

Thanks for your help.

Upvotes: 0

Views: 46

Answers (3)

Ramesh
Ramesh

Reputation: 1484

The query below excludes the RED rows. 

SELECT v_all.id_association_mots_idM,
       v_all.id_vocab_idM,
       v_all.id_vocab_idM,
       v_all.mot_v
  FROM (SELECT  ass_m_m.id_association_mots_idM, 
                ass_m_m.id_vocab_idM, 
                v_a_m.categorie_v , 
                v_a_m.mot_v 
        FROM  `association_mots_login` AS ass_m_l,  
              `association_mots_id_mot` AS ass_m_m, 
              `vocabulaire_a_memoriser` AS v_a_m 
        WHERE ass_m_l.id_association_mots  = ass_m_m.id_association_mots_idM
          AND ass_m_l.id_login_mL = '1'
          AND ass_m_m.id_vocab_idM = v_a_m.id_vocab 
          AND v_a_m.categorie_v IN ('langue_francais', 'langue_anglais')) v_all,
        (SELECT DISTINCT ass_m_m.id_association_mots_idM
          FROM  `association_mots_login` AS ass_m_l,  
                `association_mots_id_mot` AS ass_m_m, 
                `vocabulaire_a_memoriser` AS v_a_m 
          WHERE ass_m_l.id_association_mots  = ass_m_m.id_association_mots_idM
            AND ass_m_l.id_login_mL = '1'
            AND ass_m_m.id_vocab_idM = v_a_m.id_vocab 
            AND v_a_m.categorie_v = 'langue_anglais') v_eng 
WHERE v_all.id_association_mots_idM = v_eng.id_association_mots_idM        ;

Explanation:

In-line view v_all is same as what you had. Gets all the records for both langue_francais and langue_anglais. Inline view v_eng only gets the records where there is a traduction in english i.e. langue_anglais. Inner joining the 2 views would give you just the records with english traduction.

Hope this helps!

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74740

You're really going to have to start formatting and indenting your SQLs nicely if you want them to be maintainable in future

The following query excludes the red rows:

SELECT 
  ass_m_m.id_association_mots_idM, 
  ass_m_m.id_vocab_idM, 
  v_a_m.categorie_v , 
  v_a_m.mot_v 

FROM 
`association_mots_id_mot` AS ass_m_m
INNER JOIN
`association_mots_login` AS ass_m_l
ON 
  ass_m_m.id_association_mots_idM = ass_m_l.id_association_mots AND 
  ass_m_l.id_login_mL = '1'

INNER JOIN 
`vocabulaire_a_memoriser` AS v_a_m 
ON
  ass_m_m.id_vocab_idM = v_a_m.id_vocab AND 
  (v_a_m.categorie_v = 'langue_francais' OR v_a_m.categorie_v = 'langue_anglais')

INNER JOIN
(
  SELECT 
    ass_m_m.id_association_mots_idM 
  FROM 
    `association_mots_id_mot` AS ass_m_m
    INNER JOIN 
    `vocabulaire_a_memoriser` AS v_a_m 
    ON
      ass_m_m.id_vocab_idM = v_a_m.id_vocab AND 
      (v_a_m.categorie_v = 'langue_francais' OR v_a_m.categorie_v = 'langue_anglais')
   GROUP BY
     ass_m_m.id_association_mots_idM
   HAVING COUNT(distinct categorie_v) = 2  
) wanted
ON
  ass_m_m.id_association_mots_idM = wanted.id_association_mots_idM

I've converted the query to ANSI join syntax - please avoid using the old SELECT * FROM a,b,c,d WHERE a.id=b.id ... syntax

The top part of your query is your query (converted join syntax, and laid out indented so we have a chance of seeing what's going on) unchanged

The bit that does the work is this subquery:

(
  SELECT 
    ass_m_m.id_association_mots_idM 
  FROM 
    `association_mots_id_mot` AS ass_m_m
    INNER JOIN 
    `vocabulaire_a_memoriser` AS v_a_m 
    ON
      ass_m_m.id_vocab_idM = v_a_m.id_vocab AND 
      (v_a_m.categorie_v = 'langue_francais' OR v_a_m.categorie_v = 'langue_anglais')
   GROUP BY
     ass_m_m.id_association_mots_idM
   HAVING COUNT(distinct categorie_v) = 2  
) wanted

It reduces the ass_m_m.id_association_mots_idM list of 2,2,5,5,5,476,478 to just 2,5 because they are the only ass_m_m.id_association_mots_idM that have a COUNT(DISTINCT language) of 2 - i.e. they're the only rows where 2 distinct language values appear

Then, because the output of this query is just 2,5 when it's joined into the main, it restricts the main query to just rows where the iass_m_m.id_association_mots_idM is 2 or 5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Is this what you want?

select t.*
from t
where exists (select 1
              from t t2
              where t2.id_association_mots_idm = t.id_association_mots_idm and
                    t2.category_v = 'langue_anglais'
             );

Upvotes: 0

Related Questions