CODEWITHSUNDEEP
sqlms-accessleft-join
JLS
JLS

Reputation: 71

SQL problems, JOINS

Im having some problem with my SQL code. My assignment is to present some information about teachers(Lärare.personnummer) who don't have teach the course "Java2"(Kurstilfälle.kurs). The code is right but my problem is that there is one teacher who don't have teach any course(kurs). So the information about her is not in the result. I want to get this last persons information in my result.

My code--> https://i.sstatic.net/Pu6uj.jpg

Database--> https://ilearn2.dsv.su.se/mod/resource/view.php?id=21941

SELECT DISTINCT Person.personnummer, Person.namn, tjänsterum, telefon
FROM Kurstillfälle,
     Person,
     Lärare
WHERE Person.personnummer = Lärare.personnummer
  AND Kurstillfälle.lärare = Person.personnummer
  AND Lärare.personnummer NOT IN (SELECT Kurstillfälle.lärare
                                  FROM Kurstillfälle WHERE kurs = 'Java2')

Maybe someone can help me with this. Thanks.

Upvotes: 2

Views: 73

Answers (2)

jarlh
jarlh

Reputation: 44766

With new style JOIN, return a teacher who NOT EXISTS as teacher for Java2:

SELECT p.personnummer, p.namn, tjänsterum, telefon
FROM Person p
  INNER JOIN Lärare l ON p.personnummer = l.personnummer
WHERE NOT EXISTS (SELECT 1 FROM Kurstillfälle
                  WHERE kurs = 'Java2'
                    AND lärare = p.personnummer)

Edit: I don't know Access syntax, but try INNER JOIN instead of just JOIN!

Upvotes: 3

Matt
Matt

Reputation: 15071

Using Joins this should get what you want

SELECT DISTINCT p.personnummer, p.namn, tjänsterum, telefon
FROM Kurstillfälle k
INNER JOIN Person p ON k.larare = p.personnummer
INNER JOIN Lärare l ON p.personnummer = l.personnummer
WHERE k.kurs !='Java2'

Upvotes: 0

Related Questions