Search code examples
sqlignite

Select column where value appears in an given array and where the column value exists for all array entries

Im working on an sql statement but my sql knowledge seems not to be complexe enough to achieve my goals. I have a table structure like the following:

ID    INTERVAL
1     2014-11-12/2014-11-19
1     2015-11-12/2015-11-19
2     2014-11-12/2014-11-19
3     2016-11-12/2016-11-19

Now I have an Array of ID's containing:

[1, 2, 3]

And I want to get all intervals of these ids where each interval is existing for all ids. I started with the follwing statement:

SELECT INTERVAL FROM INTERVALS WHERE ID IN (1, 2, 3)

This ofcourse will return:

2014-11-12/2014-11-19
2015-11-12/2015-11-19
2014-11-12/2014-11-19
2016-11-12/2016-11-19

But now I additionally only want to return those results where there is the same interval existing for each id. So in this case I would expect no result at all. But If we have this dataset for example:

ID    INTERVAL
1     2014-11-12/2014-11-19
1     2015-11-12/2015-11-19
2     2014-11-12/2014-11-19
3     2014-11-12/2014-11-19
3     2016-11-12/2016-11-19

The result of the query should be

2014-11-12/2014-11-19

because only this interval is existing for all of the given ids. How do I have to change my sql statement to achieve that?

I also tried:

SELECT INTERVAL FROM INTERVALS A 
    WHERE A.ID in (1, 2, 3)
    AND EXISTS (
                SELECT INTERVAL FROM INTERVALS B
                WHERE A.INTERVAL = B.INTERVAL
               )

Edit

Im using Apache Ignite Queries

Solution

  • You can do something like that:

    SELECT interval
FROM intervals
WHERE id IN(1, 2, 3)
GROUP BY interval
HAVING COUNT(DISTINCT id) = 3;

    But I'm not sure, that it will have good performance. If you want it to run fast, you can make cache, that contains intervals table, replicated. Also consider replacing IN operator with JOIN, as explained here: https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations