Check if any value in list satisfies a condition-CodePudding

Suppose I have the following table called Seasons:

...	start_month	end_month
...	2	6
...	3	4
...	...	...

I need to write a query which, for a given list of months, returns all the Seasons that satisfy the condition where at least 1 month in the list is: start_month <= month <= end_month.

I've written this query as a native query with JDBC, except the where clause.

@Repository
public class SeasonsRepositoryImpl implements SeasonsRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<SeasonsProjection> findByMonths(List<Integer> months) {
        String query = "select * "  
                       "from seasons as s"  
                       "where ...."

        try {
            return  em.createNativeQuery(query)
                    .setParameter("months", months)
                    .unwrap(org.hibernate.query.NativeQuery.class)
                    .setResultTransformer(Transformers.aliasToBean(SeasonsProjection.class))
                    .getResultList();
        } catch (Exception e) {
            log.error("Exception with an exception message: {}", e.getMessage());
            throw e;
        }
    }

}

I have no idea how to write this, I thought of using the ANY operator until I found out that ANY only works with tables and not lists, I thought of writing this as a subquery with converting the list to a table, but I don't know if that's possible, I couldn't find anything in the MySQL documentation.

CodePudding user response：

In your where clause, you will want to have an OR conditions for each month.
You can generate it in a loop and then concat or something similar.

Your final query for input of 1,3,4,7 for example will look as follows:

SELECT *
FROM seasons as s
WHERE (1 between start_month and end_month
OR 3 between start_month and end_month
OR 4 between start_month and end_month
OR 7 between start_month and end_month)

CodePudding user response：

One way to accomplish this is:

select s.* 
from (select 1 as mn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8
   union select 9 union select 10 union select 11 union select 12) as a
inner join season s on a.mn between s.start_month and s.end_month
where a.mn in (:flexibleTypeMonths);