Check if any value in list satisfies a condition

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.

Answer

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

Advertisement

Answer