Hier stoßen wir auf scheinbar einfache JOIN-Probleme.

Ich habe zwei Tabellen, Benutzer und Kurse

| users.id | users.name |
| 1        | Joe        |
| 2        | Mary       |
| 3        | Mark       |
| courses.id | courses.name |
| 1          | History      |
| 2          | Math         |
| 3          | Science      |
| 4          | English      |

Und eine andere Tabelle, die die beiden verbindet:

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

Ich versuche, unterschiedliche Benutzernamen zu finden, die sich in Kurs 1 und Kurs 2 befinden

Es ist möglich, dass ein Benutzer auch an anderen Kursen teilnimmt, aber es ist mir nur wichtig, dass er mindestens in 1 und 2 ist

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")

Ich verstehe, warum dies eine leere Menge zurückgibt (da keine einzelne verbundene Zeile Verlauf und Mathematik hat - es hat nur einen Wert pro Zeile.

Wie kann ich die Abfrage so strukturieren, dass sie "Joe" zurückgibt, da er an beiden Kursen teilnimmt?

Update - Ich hoffe, dass ich die erwartete Gesamtzahl der Kurse für einen bestimmten Benutzer nicht fest codieren kann, da sie sich möglicherweise in anderen Kursen befinden, die meiner Suche nicht wichtig sind.

1
d-_-b 22 Feb. 2020 im 20:00

5 Antworten

Beste Antwort

Verbinden Sie Benutzer mit einer Abfrage, die die Benutzer-IDs in beiden Kursen zurückgibt:

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

Sie können verschiedene von der Bedingung weglassen:

count(distinct courses_id) = 2

Wenn in users_courses keine Duplikate vorhanden sind.
Siehe die Demo.

Wenn Sie nach Kursnamen und nicht nach IDs suchen möchten:

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

Siehe die Demo.
Ergebnisse:

| name |
| ---- |
| Joe  |
3
forpas 22 Feb. 2020 im 17:16

Ich würde schreiben:

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
;

Bei komplexeren Bedingungen (z. B. wenn der Benutzer in bestimmten Klassen, aber auch nicht in bestimmten Klassen wie "Wissenschaft" sein muss) sollte dies auch funktionieren:

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
)
;
0
Nae 22 Feb. 2020 im 19:12

Ihr Code ist nah. Verwenden Sie einfach GROUP BY und eine HAVING -Klausel:

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;

Anmerkungen:

  • Dies setzt voraus, dass Benutzer nicht denselben Namen haben können. Möglicherweise möchten Sie GROUP BY u.id, u.name verwenden, um sicherzustellen, dass Sie einzelne Benutzer zählen.
  • Wenn Benutzer denselben Kurs nicht mehrmals belegen können, verwenden Sie COUNT(*) = 2 anstelle von COUNT(DISTINCT).
0
Gordon Linoff 22 Feb. 2020 im 17:26

Fast ähnlich wie bei @ Naes Lösung.

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);
0
Sithroo 22 Feb. 2020 im 17:20

Sie können in operator und select verwenden, um eine Liste potenzieller Benutzer-IDs zu erstellen, die am zweiten Kurs teilnehmen, und passende im ersten Kurs zu finden. Dies ist um ein Vielfaches schneller als die Verwendung von Joins.

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
MaxT 22 Feb. 2020 im 17:08