MySQL query not getting the desired result

Question

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

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

Desired result:

Verkoop & Administratief
Design & Ontwikkeling | Wilhelmus Frederikus Aloisius | van | Hot naar Her | Directeur

You need to provide sample data and desired results. Your description is a bit difficult because of the language issue. — Gordon Linoff
– Gordon Linoff, Commented Nov 19, 2017 at 16:26
Inner joins are exclusive; if there is no result in one of the joined tables, you won't get a row in your result. What happens if you use left joins instead? — rickdenhaan
– rickdenhaan, Commented Nov 19, 2017 at 16:42

dmfay · Accepted Answer · 2017-11-19 18:39:14Z

1

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.

answered Nov 19, 2017 at 18:39

dmfay

2,4771 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query not getting the desired result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related