Using Distinct or Case to reduce results in query

Question

The scenario, which is a simple database about tennis, is:

• Select male players who have atleast two 'e' in their name. (This part fine)

• Show a list of them who currently have a functie(role) in the table board members (called bestuursleden). I've deduced this is when the column eind_datum (end date) is NULL for logical reasons as is in my query.

• Male players currently without a functie (function) should also be showed. This part where the problem is.

Expected Result:

naam (name)  | geslacht (gender)|   functie (role)
_____________|__________________|_______________
Permentier   | M                | Voorzitter
Bakker, de   | M                | NULL
Bohemen, van | M                | NULL
Meuleman     | M                | Penningmeester
Permentier   | M                | NULL

My Result (I am missing the name Bohemen, van):

naam (name)  | geslacht (gender)|   functie (role)
_____________|__________________|_______________
Permentier   | M                | Voorzitter
Bakker, de   | M                | NULL
Meuleman     | M                | Penningmeester
Permentier   | M                | NULL

My query:

SELECT naam AS role, geslacht AS gender, functie AS role
FROM spelers s LEFT JOIN bestuursleden b ON s.spelersnr = b.spelersnr
WHERE geslacht LIKE 'M' AND LOWER(naam) LIKE '%e%e%' AND eind_datum IS NULL
ORDER BY s.spelersnr

If I omit the WHERE check AND eind_datum IS NULL I get the following result, note I have added two columns for extra information (begin date and end date) to help determine wheter I could use a CASE or perhaps a specific DISTINCT function to help me keep the Bohemen, van record which is missing in my current query result:

naam (name)  | geslacht (gender)|   functie (role)| begin_date | end_date
_____________|__________________|_________________|____________|___________
Permentier   | M                | Secretaris      |  NOT NULL  | NOT NULL
Permentier   | M                | Lid             |  NOT NULL  | NOT NULL
Permentier   | M                | Penningmeester  |  NOT NULL  | NOT NULL
Permentier   | M                | Voorzitter      |  NOT NULL  | NULL
Bakker, de   | M                | NULL            |  NULL      | NULL
Bohemen, van | M                | Secretaris      |  NOT NULL  | NOT NULL    
Meuleman     | M                | Penningmeester  |  NOT NULL  | NULL
Permentier   | M                | NULL            |  NULL      | NULL

Its Postgres, using pgadmin as tool.

My Table definitions:

Answer 1

So as far as I understand, you want to display players and their current role, if they hold one. It's then better to include your end_date condition in the join statement, like so:

SELECT s.naam AS role
     , s.geslacht AS gender
     , b.functie AS role
  FROM spelers s 
  LEFT 
  JOIN bestuursleden b 
    ON s.spelersnr = b.spelersnr
   AND b.eind_datum IS NULL
 WHERE s.geslacht = 'M' 
   AND LOWER(s.naam) LIKE '%e%e%'
 ORDER BY s.spelersnr

This will only join the bestuursleden rows that have a NULL end date, meaning the most current one. If a player has an end date set, it won't be joined, so you will get NULLs in fields from the bestuursleden.

Note: ALWAYS use table aliases when referring to column names in queries that have a join. Otherwise people who are not familiar with your db will have no idea where is a given column coming from.