How can I get the rows that matches all values of a list passed as a parameter?

Question

I have a java method that receives a list of strings as a parameter and I want to get the row(s) that matches exactly all the values of the list (no more, no less).

I have two tables (a Cases table and a Products table) that has a many-to-many relationship.

CASES_TABLE
ID    CASES_COMMENT       ...
1      Some comment       ...
2      Some comment2      ...
3      Some comment3      ...
...        ...

PRODUCTS_TABLE
ID   PRODUCT_CODE
1     VoIP
2     IPTV
3     PSTN
...    ...

 CASES_PRODUCTS_TABLE
CASE_ID   PRODUCT_ID
1         1
2         1
2         2
2         3
3         1
3         2
...    ...

I'm using Oracle as the rdbms.

For instance:

Given the list:
  List<String> products = Stream.of("VoIP", "IPTV","PSTN").collect(Collectors.toList());;

And the method String getSomeValue (List products);

What I'd want get is the case with the id 2, because that's the one that matches exactly all the values (not the case with the id 1 because it matches only one value, and not the case with the id 2 because it matches only two values).

I've read about relational division, but I thinks it doesn't solve my problem. I've also try with the IN condition but it performs an 'OR' and I want my query to filter exactly by all values without leaving one left or getting a row that has a value that wasn't present on the list.

I tried the following:

SELECT CASES_COMMENT
FROM CASES_TABLE CT JOIN CASES_PRODUCTS_TABLE CPT
ON CT.ID = CPT.CASE_ID
JOIN PRODUCTS_TABLE
ON PT.ID = CPT.PRODUCT_ID
WHERE PRODUCT_CODE IN ('VoIP', 'PSTN', 'IPTV');

Explain your question with some sample data and expected results - will make things clearer — JGFMK, Oct 31 '18 at 14:21
I'm not sure this is supported by JQL but HQL has [collection expressions](https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#hql-collection-expressions) that might provide what you need. — Thomas, Oct 31 '18 at 14:31
@TheImpaler I explained it on a coment, now I posted the query. Thanks — ericgramirez, Oct 31 '18 at 14:38

score 0 · Answer 1 · answered Oct 31 '18 at 15:11

I think the only way to do it with JQL is with sub queries

Example:

SELECT CASES_COMMENT
FROM (SELECT * 
      FROM (SELECT *
            FROM CASES_TABLE CT JOIN CASES_PRODUCTS_TABLE CPT
            ON CT.ID = CPT.CASE_ID
            JOIN PRODUCTS_TABLE
            ON PT.ID = CPT.PRODUCT_ID
            WHERE PRODUCT_CODE = 'VoIP') as cond1
      WHERE cond1.PRODUCT_CODE = 'PSTN') as cond2
WHERE cond2.PRODUCT_CODE = 'IPTV'

Of course u can create it dynamically to fit as many strings as u want.

score 0 · Answer 2 · answered Oct 31 '18 at 15:22

This can be done in multiple ways (Analytic function being one of them) Look at this answer for hints Oracle sql query with all mandatory values in where condition

One solution by reusing your own query is

select CASES_COMMENT from
(select CASES_COMMENT , count(*) occurance from (
SELECT CASES_COMMENT
FROM CASES_TABLE CT JOIN CASES_PRODUCTS_TABLE CPT
ON CT.ID = CPT.CASE_ID
JOIN PRODUCTS_TABLE PT
ON PT.ID = CPT.PRODUCT_ID
WHERE PRODUCT_CODE IN ('VOIP', 'PSTN', 'IPTV'))
group by cases_comment)
where occurance = 3;

For this query you would need another input, which is the length or size of the input Array you are looking for

Sql Fiddle Link with example: http://sqlfiddle.com/#!4/29cd9/5

Apparently it does not work when it has all the products on the query plus a few more (or at least 1 more) — ericgramirez, Oct 31 '18 at 18:26
What do you mean? did you hard code the number '3'?, it should be equal to no of products in input. — user1933888, Oct 31 '18 at 20:22

score 0 · Answer 3 · answered Oct 31 '18 at 15:24

0

I think that grouping and counting should produce what you need:

select
    cp.case_id
  from products_table p
  join cases_products_table cp on cp.product_id = p.id
  where p.product_code in ('VoIP', 'PSTN', 'IPTV')
  group by cp.case_id
  having count(*) = 3

This query should return only CASE_ID = 2, since that's the only one that has three products.

answered Oct 31 '18 at 15:24

The Impaler

45,731
9
39
76

I'm finding that it also returns the IDs of the cases that not only has the three products mentioned but also other products. – ericgramirez Oct 31 '18 at 17:56
How so? There's a filter right there: `where p.product_code in ('VoIP', 'PSTN', 'IPTV')` What other products is the query finding? – The Impaler Oct 31 '18 at 17:58
Trying it out in "real life" it also returns the id of the cases that has as products: VoIP, PSTN, IPTV and DTH. – ericgramirez Oct 31 '18 at 18:01

How can I get the rows that matches all values of a list passed as a parameter?

3 Answers3