1
votes

MySQL où la valeur jointe est plusieurs AND

Vous rencontrez ici des problèmes de JOIN apparemment simples.

J'ai deux tables, des utilisateurs et des cours

SELECT DISTINCT(users.name)
FROM users_courses
LEFT JOIN users ON users_courses.users_id = users.id
LEFT JOIN courses ON users_courses.courses_id = courses.id
WHERE courses.name = "History" AND courses.name = "Math"
AND courses.name NOT IN ("English")

| users_id | courses_id |
| 1        | 1          |
| 1        | 2          |
| 1        | 3          |
| 2        | 1          |

et une autre table qui rejoint les deux:

| courses.id | courses.name |
| 1          | History      |
| 2          | Math         |
| 3          | Science      |
| 4          | English      |

J'essaie de trouver des noms d'utilisateur distincts qui sont dans le cours 1 et le cours 2

Il est possible qu'un utilisateur participe également à d'autres cours, mais je me soucie seulement qu'il soit en 1 et 2 au minimum

| users.id | users.name |
| 1        | Joe        |
| 2        | Mary       |
| 3        | Mark       |

I comprendre pourquoi cela renvoie un ensemble vide (puisqu'aucune ligne jointe ne contient History et Math - il n'a qu'une seule valeur par ligne.

Comment puis-je structurer la requête pour qu'elle renvoie "Joe" parce qu'il est dans les deux cours?

Mettre à jour - J'espère éviter de coder en dur le nombre total de cours attendu pour un utilisateur donné, car ils pourraient être dans d'autres cours dont ma recherche ne se soucie pas.

sql mysql

2 commentaires

Notez que DISTINCT n'est pas une fonction

À partir de maintenant, chaque réponse (à l'exception peut-être de stackoverflow.com/a/60354623/7032856 en raison d'une erreur éventuellement) compte pour l'exigence des utilisateurs devant être "en 1 et 2 au minimum" .

5 Réponses :

3
votes

Joindre les utilisateurs à une requête qui renvoie les identifiants des utilisateurs des deux cours:

| name |
| ---- |
| Joe  |

Vous pouvez omettre distinct de la condition:

select u.name
from users u 
inner join (
  select uc.users_id
  from users_courses uc inner join courses c
  on c.id = uc.courses_id
  where c.name in ('History', 'Math')
  group by uc.users_id
  having count(distinct c.id) = 2
) c on c.users_id = u.id

s'il n'y a pas de doublons dans users_courses.
Consultez la démo .

Si vous souhaitez rechercher par nom de cours et non par identifiant:

count(distinct courses_id) = 2

Voir le démo .
Résultats:

select u.name
from users u 
inner join (
  select users_id
  from users_courses
  where courses_id in (1, 2)
  group by users_id
  having count(distinct courses_id) = 2
) c on c.users_id = u.id

7 commentaires

Merci - mais que faire s'ils suivent des cours supplémentaires autres que les 2 que je recherche?

Si vous souhaitez rechercher 3 cours, tout ce que vous avez à faire est de les inclure dans la liste IN et de passer à: ayant count (...) = 3

Moins de recherche pour les 3 cours - je souhaite toujours rechercher 2 cours, ces utilisateurs peuvent être dans d'autres en plus de ceux qui ne me préoccupent pas dans ma recherche.

Mon code fonctionne également pour ce cas. Vérifiez ceci: db-fiddle.com/f/9EX4LyKgNscpVt2FMZyYJM/4 pour votre données modifiées.

Merci - cela fonctionne! Que faudrait-il changer pour prendre également en charge AND c.name NOT IN ("Science") par exemple?

Vérifiez ceci: db-fiddle.com/f/9EX4LyKgNscpVt2FMZyYJM/5

Ou ceci: db-fiddle.com/f/9EX4LyKgNscpVt2FMZyYJM/6

0
votes

J'écrirais:

SELECT MAX(u.name)
FROM users_courses uc
LEFT JOIN users u ON uc.users_id = u.id
GROUP BY uc.users_id
HAVING (
  SUM(uc.courses_id = 1) = 1
  -- user enrolled exactly once in the course 2
  AND SUM(uc.courses_id = 2) = 1
  -- user enrolled in course 3, 0 times
  AND SUM(uc.courses_id = 3) = 0
)
;

Pour des conditions plus complexes (par exemple exigeant que l'utilisateur soit dans certaines classes mais pas non plus dans certaines classes telles que "Science") ceci devrait également fonctionner:

SELECT MAX(u.name)
FROM users_courses uc
LEFT JOIN users u ON uc.users_id = u.id
WHERE uc.courses_id IN (1, 2)
GROUP BY uc.users_id
HAVING COUNT(0) = 2
;

5 commentaires

@Akina Pouvez-vous en dire un peu plus? Qu'est-ce que vous voulez dire?

LEFT JOIN donnera les utilisateurs qui ont les cours 1 et 2, plus les enregistrements des paires utilisateur-cours pour lesquels l'enregistrement utilisateur est perdu (est absent dans le tableau des utilisateurs). Mais OP a besoin de noms d'utilisateur, c'est-à-dire d'utilisateurs existants uniquement. Vous devez donc utiliser INNER JOIN, pas LEFT.

@Akina Je vois, vous dérivez les utilisateurs existants uniquement du fait qu'ils ont besoin du nom, non? Je ne pense pas qu'OP ait explicitement déclaré cela, en plus d'utiliser eux-mêmes la jointure gauche dans leur requête. Je dois donc être en désaccord. C'est une de mes habitudes d'utiliser la jointure gauche quand je n'ai besoin que de la table pour des informations détaillées.

Pourquoi utiliseriez-vous count (0) ? Cela a l'air tellement gênant. Vous pouvez utiliser count (*) ou count (1) ou sum (1) .

@GordonLinoff Je viens de créer une vue avec count (*) avant et la base de données l'a convertie en count (0) . J'ai hérité de ça depuis. Cependant, si je n'avais pas vu cela, j'irais COUNT (NULL) .

0
votes

Vous pouvez utiliser in operator et utiliser select pour générer la liste des users_id potentiels participant au deuxième cours, afin de trouver ceux qui correspondent au premier cours. C'est beaucoup plus rapide que d'utiliser des jointures.

select distinct u.users_id, users.name
from users_courses u, users 
where u.users_id in (select distinct users_id from users_courses where courses_id = 2)
and u.courses_id = 1
and users.users_id = u.users_id

0 commentaires

0
votes

Presque similaire à la solution de @ Nae.

select u.name from users u 
where exists 
  (select 1 
   from users_courses uc 
   where uc.course_id in (1, 2) 
   and uc.user_id = u.id 
   group by uc.user_id 
   having count(0) = 2);

1 commentaires

Merci - mais que faire s'ils suivent des cours supplémentaires autres que les 2 que je recherche?

0
votes

Votre code est proche. Utilisez simplement GROUP BY et une clause HAVING :

SELECT u.name
FROM users_courses uc JOIN
     users u
     ON uc.users_id = u.id JOIN
     courses c
     ON uc.courses_id = c.id
WHERE c.name IN ('History', 'Math')
GROUP BY u.name
HAVING COUNT(DISTINCT c.name) = 2;

Remarques:

Cela suppose que les utilisateurs ne peuvent pas avoir le même nom. Vous pouvez utiliser GROUP BY u.id, u.name pour vous assurer que vous comptez des utilisateurs individuels.
Si les utilisateurs ne peuvent pas suivre le même cours plusieurs fois, utilisez COUNT (*) = 2 plutôt que COUNT (DISTINCT) .

2 commentaires

Merci - cependant, que se passe-t-il s'ils suivent des cours supplémentaires mais que je ne vérifie toujours que ces 2?

@d -_- b. . Ajoutez-les simplement à la liste et changez la valeur de "2".