How to make correct query with complicated conditions?-CodePudding

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?

CodePudding user response：

 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`)
                                          )