Q:

Query membership of group on date with open-ended memberships

Given the following table structure for tracking membership of given groups:  
+----+----------+----------------+--------------+  
| id | group_id | in_group_begin | in_group_end |  
+----+----------+----------------+--------------+  
|  1 |       10 | 2019-01-01     | 2019-02-01   |  
|  1 |       11 | 2019-02-02     | 2019-03-01   |  
|  1 |       12 | 2019-03-01     | NULL         |  
|  2 |       10 | 2019-01-01     | NULL         |  
+----+----------+----------------+--------------+  

(Where in_group_end being NULL signifies this is their current group)  
How would I form a query that would tell me, for example, what group_id each member was associated with on a given date? 
... in_group_end IS NULL will give me their current group, not necessarily the group they were in
... in_group_end IS NULL OR in_group_end >= '{$date_str}' could give me multiple options  
Ideally I would like something I can use in a joined query, e.g. with a table storing a persons name, address, etc. from which I expect only one row back.  
Would some kind of IF stmt in the JOIN do it? or GROUP in a sub-query? 

A:

Consider the following logic, which would find all matches for 2019-01-15:
SELECT group_id
FROM yourTable
WHERE
    '2019-01-15' >= in_group_begin AND
    ('2019-01-15' <= in_group_end OR in_group_end IS NULL);

The WHERE clause considers an input date a match if it lies in between the start and end dates or it is greater than the start date and there is no end date.  Also, the WHERE clause as written can make use of an index.

