How to make correct query with complicated conditions?

I have this table:

id  band_id musician_id date_join   date_left   band_name    active_years_begin active_years_end
1   2       7           1960-01-01  1970-12-31  The Beatles  1960-01-01         1970-12-31
2   2       8           1960-01-01  1970-12-31  The Beatles  1960-01-01         1970-12-31
3   2       9           1960-01-01  1970-12-31  The Beatles  1960-01-01         1970-12-31
4   2       10          1960-01-01  1970-12-31  The Beatles  1960-01-01         1970-12-31
5   3       9           1971-12-31  1981-01-01  Wings        1971-12-31         1981-01-01
6   6       5           2017-08-01  NULL        Band1        2017-08-01         NULL
7   7       6           2019-09-01  NULL        Band2        2019-09-01         NULL
8   4       5           2018-09-01  NULL        Band3        2018-09-01         NULL
9   5       5           2018-09-01  NULL        Band4        2018-09-01         NULL
10  9       5           2021-09-01  NULL        Band5        2018-09-01         NULL

I need to select only that band_names which participant set is not changed all time of its existance(date_join == active_years_begin and (date_left is null or active_years_end is null) or (date_left == active_years_end)) ) for all band participants.

So here right answer is The Beatles, Wings, Band1, Band2, Band3, Band4.

How can I do that?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

 SELECT DISTINCT band_name 
 FROM year_tbl as a 
 WHERE a.date_join IN (
                       SELECT `active_years_begin` 
                       FROM `year_tbl`) 
                       AND ((a.date_left IS NULL 
                       OR a.active_years_end IS NULL) 
                       OR a.date_left IN (
                                          SELECT active_years_end 
                                          FROM `year_tbl`)
                                          )

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0