1

I would like to write an SQL script like this:

select count(*) from table1 t1 where exists (
    select t2.desired_col from table2 t2 where t1.ID = t2.reference_t1_id 
        intersect 
    (2, 5, 9, 10)
);

The goal is to tell me, how many entries in t1 have at least one common value between desired_col of t2 and a given collection (i.e. (2, 5, 9, 10)). t2 has a ManyToOne relationship to t1. However, it seems I cannot intersect between a select and a simple collection. As a workaround, I wrapped the given collection like this:

select count(*) from table1 t1 where exists (
    select t2.desired_col from table2 t2 where t1.ID = t2.reference_t1_id 
        intersect  
    select desired_col from table t2 where desired_col in (2, 5, 9, 10)
);

I think this solution looks a little ugly and was wondering, if there is a better way to find the intersect between a select statement and a simple collection.

hildebro
  • 549
  • 2
  • 5
  • 20
  • Please edit your question to provide some sample data for your table1, table 2 and your collection, plus the expected output for the rows. – Boneist Mar 13 '19 at 12:29
  • @Boneist I don't see how sample data would make this any easier to answer. I'm simply asking for a pretty way to intersect between a select statement and a simple collection. The code in my question is already an example of how I want things to look and what they look like currently. – hildebro Mar 13 '19 at 12:40
  • I asked for data so it's easier to see what you're trying to do, and easier for us to recreate your scenario. Also, you didn't say where your "collection" is coming from. Parameters? An array from PL/SQL? – Boneist Mar 13 '19 at 12:43

1 Answers1

3

You need to turn your list of IDs into an actual set, which you can then use in SQL with a table() function. Something like this: 

select count(*) from table1 t1 
where exists (
    select t2.desired_col from table2 t2 
    where t1.ID = t2.reference_t1_id 
    intersect
     select * from table(sys.odcinumberlist (2, 5, 9, 10))
);

There are several variant solutions. For instance, instead of intersection you could extend the WHERE clause with an IN subquery: 

and t2.desired_col in (select * from table(sys.odcinumberlist (2, 5, 9, 10)))

or indeed 

and t2.desired_col in (2, 5, 9, 10)

This uses the Oracle supplied collection type, sys.odcinumberlist, which is an array of numbers.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Not much prettier than my solution, but using `in` instead of `intersect` means I don't even have to convert my collection into a table anymore. In your example, I can replace everything after `in` with simply `(2 ,5 ,9, 10)` and the query executes without error. – hildebro Mar 13 '19 at 12:37
  • 1
    True. There are many ways to solve a problem like this. I was re-writing my answer even as you accepted it :) . + – APC Mar 13 '19 at 12:39