How to count one column and compare it to another With 2 Inner joins

Question

I have the following Tables:
Cursistcursus
Emailadres
Cursuscode

SoortCursus
SoortCode
Cursussoort
Prijs
Maxcursisten

Cursus
Cursuscode
Begindatum
Einddatum
Soortcode

This is my current Query:

SELECT DISTINCT Cursus.Cursuscode, SoortCursus.SoortCode, SoortCursus.Maxcursisten, Cursus.Soortcode, COUNT(Cursistcursus.Cursuscode) as Bezetting, Cursus.Begindatum, Cursus.Einddatum, SoortCursus.Cursussoort, SoortCursus.Prijs 
FROM Cursistcursus,Cursus 
     INNER JOIN SoortCursus
                ON SoortCursus.SoortCode = Cursus.Soortcode 
WHERE (SELECT COUNT(Cursuscode) FROM Cursistcursus) <= SoortCursus.Maxcursisten";

What it needs to do is the following:

I want to get the Cursussoort from Soortcursus where the Soortcode is the same. I Want to count the amount of Cursistcursus per each Cursuscode. So that I can check if there is less CursistCursus with the same Cursuscode then Maxcursisten.

I Have been trying for over 4 hours now!

Sample Data:

Cursus
(1, 2016-11-13, 2016-11-19, BEG)
(2, 2016-11-13, 2016-11-19, GEV)
(3, 2016-11-13, 2016-11-19, WAD)

SoortCursus
(BEG, Beginnerscursus, 700.00, 7)
(GEV, Gevorderdencursus, 800.00, 5)
(WAD, Waddentocht, 900.00, 10)

CursistCursus
(1, Test@test.com)
(1, Test@test.com)
(1, Test@test.com)
(1, Test@test.com)
(1, Test@test.com)
(1, Test@test.com)
(1, Test@test.com)
(2, Test2@test.com)

SELECT DISTINCT Cursus.Cursuscode, SoortCursus.SoortCode, SoortCursus.Maxcursisten, Cursus.Soortcode, Cursus.Begindatum, Cursus.Einddatum, SoortCursus.Cursussoort, SoortCursus.Prijs FROM Cursus INNER JOIN SoortCursus
                ON SoortCursus.SoortCode = Cursus.Soortcode WHERE (SELECT COUNT(Cursuscode) AS Bezetting FROM Cursistcursus) < SoortCursus.Maxcursisten GROUP BY Cursuscode

This gives me the right Cursussoort but then every Maxcursisten gets 7!

Answer 1

Do you need use THAT names? ._. Looks like a riddle.

However, give us some more info. What's the PK in each table?

If you do this (more info and clearly names), maybe we can help you better.