CODEWITHSUNDEEP
sqlwhere-clauseequality
Zarkoffe
Zarkoffe

Reputation: 39

Where there is no equality (SQL)

I have wrote this clause:

WHERE ec.etudiant = po.utilisateurEtudiant

With this, i have the student where this equality. Now, i would like to transform this request. I want to get "For each student with the id "ec.etudiant" that i don't find in "po.utilisateurEtudiant"... i do something. How can i get it? If possible avoid LEFT JOIN ON: i am looking for a solution in the WHERE directly (for a specific reason linked to Symfony and my code).

Thank you for your answers. 7,8,9 was exemple. I need a real dynamic request which is able to only keep the difference between "po.utilisateurEtudiant" (contain for EXAMPLE 7,8) and "ec.etudiant"(contain 7,8,9, so i want that my request return me ONLY 9). And only keep the part of "po.utilisateurEtudiant"that ""ec.etudiant" doesn't contain. I tried with NOT EXISTS for 1 hours and i never get what i want :/ My complet request (Symfony 4):

{
  return $em->createQuery(
    "SELECT u.nomUtilisateur, u.prenomUtilisateur, ec.id AS ec_id
    FROM App\Entity\CoursPlanning h
    INNER JOIN App\Entity\Cours c
    JOIN App\Entity\ProfCours p
    JOIN App\Entity\Utilisateur u
    JOIN App\Entity\CoursPlanning cp
    JOIN App\Entity\EtudiantCours ec
    JOIN App\Entity\PlageHoraire w
    JOIN App\Entity\DateCours d
    JOIN App\Entity\Formation f
    JOIN App\Entity\Pointage po
    WHERE u.roles = '[\"ROLE_USER\"]'
    AND cp.cours = '".$idCours."'
    AND cp.dateCours = '".$idDate."'
    AND po.cours = cp.id
    AND po.plageHoraire = cp.plageHoraire
    AND po.utilisateurEtudiant = u.id
    AND NOT EXISTS (SELECT 1 FROM App\Entity\EtudiantCours es
                    WHERE po.utilisateurEtudiant = es.etudiant )
    "
    )->getResult();
}

This request return me an empty result. But if I do (in the NOT EXISTS): "po.utilisateurEtudiant = 7", i get the 8. Or : if I do ""po.utilisateurEtudiant = 8", i get the 7.

Thank you for advance!

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You would use not exists:

select ec.*
from ec
where not exists (select 1
                  from po
                  where ec.etudiant = po.utilisateurEtudiant
                 );

Upvotes: 1

Related Questions