I have 3 table in a database
One contains the data about the games that are played and is defined as followed:
CREATE TABLE tbl_Wedstrijden (Wed_ID Word PRIMARY KEY ASC, Wed_Seizoen_ID Word, Wed_Datum DATE, Wed_Tijd TIME, Wed_Plaats Word, Wed_TeamA Word, Wed_TeamB Word, Wed_Scheids_1 Word, Wed_Scheids_2 Word, Wed_Scheids_3 Word, Wed_Scheids_4 Word, Wed_Divisie Word, Wed_Begeleiding BOOLEAN, Wed_SPV_ID Word, Wed_Opmerkingen Text, Wed_Meetellen BOOLEAN, Wed_Show BOOLEAN, Wed_Outlook BOOLEAN, FOREIGN KEY(Wed_Seizoen_ID) REFERENCES "tbl_Seizoenen_Oud"(Sei_ID), FOREIGN KEY(Wed_TeamA) REFERENCES tbl_Teams(Team_ID), FOREIGN KEY(Wed_TeamB) REFERENCES tbl_Teams(Team_ID), FOREIGN KEY(Wed_Scheids_1) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_2) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_3) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_4) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Divisie) REFERENCES tbl_Divisie(Divisie_ID), FOREIGN KEY(Wed_SPV_ID) REFERENCES tbl_Supervisors(SPV_ID))
I have a table with licenses for the referees. It is defined as:
CREATE TABLE tbl_Licenties (Fluit_ID Word PRIMARY KEY ASC, Fluit_Seizoen_ID Word, Fluit_Scheids_ID Word, Fluit_Licentie_ID Word, Fluit_Cursus BOOLEAN, Fluit_Opmerking TEXT, Fluit_Vergoeding BOOLEAN, Fluit_Bedrag FLOAT)
And i have a table for the referees. It is defined as:
CREATE TABLE tbl_Scheidsrechters (Scheids_ID word PRIMARY KEY ASC, Voornaam VARCHAR(20), Tussenvoegsel VARCHAR(10), Achternaam VARCHAR(50), Adres VARCHAR(60), Postcode VARCHAR(6), Woonplaats VARCHAR(40), Telefoon VARCHAR(11), Mobiel VARCHAR(11), Geboortedatum DATE, Email VARCHAR(250), IBAN VARCHAR(18))
How can i count the games of each referee and if the referee has NO GAMES then a ZERO has to be assigned to that referee in that season.
So the final view has to be something like this:
Scheids_ID | Fluit_Seizoen_ID | Games