CODEWITHSUNDEEP
mysqlsql
Hoxlegion
Hoxlegion

Reputation: 277

MySQL query not getting the desired result

I am trying to get all the "afdelingen" which are 2 and the name of the "directeur" behind the "afdelingen.naam" base on his "functienaam". But I only get the "afdelingen.naam" with the name of the "Directeur".

How do I also get the other "afdelingen.naam" in this result.

This is what I currently have:

SELECT afdelingen.naam, personeel.voornamen, personeel.tussenvoegsels, personeel.achternaam, rollen.functienaam
FROM `afdelingen` INNER JOIN
     personeel_afdeling
     ON afdelingen.id = personeel_afdeling.afdeling_id INNER JOIN 
     personeel
     ON personeel_afdeling.personeel_id = personeel.id INNER JOIN
     rollen
     ON personeel_afdeling.rol_id = rollen.id
WHERE rollen.id LIKE 1

A quick overview of the tables that are used

Current result: Design & Ontwikkeling | Wilhelmus Frederikus Aloisius | van | Hot naar Her | Directeur

Desired result:

Upvotes: 1

Views: 46

Answers (1)

dmfay
dmfay

Reputation: 2477

Left joins as mentioned by @rickdenhaan are only part of it; you're also filtering for rollen.id = 1. Since the "Verkoop & Administratief" afdeling (I think that's the right singular?) does not have a Directeur, you're eliminating that row from your result set with the WHERE clause.

Instead of filtering with a WHERE, add the filter to your join condition so you only join the Directeur role. And use LEFT JOINs to ensure that you join nothing if there is no Directeur instead of removing rows from your dataset without the corresponding role.

Upvotes: 1

Related Questions