How to use IN as exactly match using SQL?

Question

I have a system that monitors phones. As we all know, phones can have one sim or two. It's a known fact that there are differents operators. So I want to show a filter with all possibles combinations of phones and operators. Today I have an universe of 10k devices. In the end the system shows devices measurements but user's may filter those statistics by phone's manufacturer (apple, samsung, nokia, etc etc), model and operators.

So I have this form that would show all currently combinations using my device universe.

In the end I have the filter like this:

<select id="filter" multiple="multiple">
      <optgroup label="Model">
            <option value="1">iPhone</otion>
            <option value="2">Samsung</otion>
            <option value="3">Asus</otion>
      </optgroup>
      <optgroup label="Operator">
            <option value="1">Digicel</otion>
            <option value="2">FLOW</otion>
            <option value="3">Rogers</otion>
            <option value="4">Telus</otion>
            <option value="5">Bell</otion>
            ...
            ...
            <option value="2,3">FLOW,Rogers</otion>
            <option value="2,5">FLOW,Bell</otion>
            <option value="3,1">Rogers,Digicel</otion>
            ...[I don't know all current combinations..this is a dynamic filter]
      </optgroup>
</select>

Database Model

So I have the main table of devices (I'm going to put only columns that "matters"):

devices
------------   
id_device (pk) | id_manufacter (fk) | id_model (fk)
------------   
 1 | 1  | 10
 2 | 1  | 13
 3 | 1  | 14
 4 | 2  | 5
 4 | 2  | 6
 ..........
 99| 60 | 811
 ..........


operators
------------   
id_operator (pk) | operator_name | operator_ip
------------   
 1 | "Digicel"  | 10.192.112.29
 2 | "FLOW"     | 10.192.112.33
 3 | "Rogers"   | 10.192.112.54
 4 | "Telus"    | 10.192.112.111
 5 | "Bell"     | 10.192.112.233
 ..........
 4654 | "Vivo"     | 10.192.112.44
 ..........

The IP address I use to do some measurements - it's irrelevant for now - and of course these are fake ips.

And I have this intermediate table:

------------   
id_device | id_operator
------------   
 1 | 1
 1 | 2
 2 | 1
 3 | 3
 4 | 2
 4 | 3
 5 | 2
 5 | 5
 6 | 2
 6 | 5
 .........
 129129 | 3
 129129 | 1

What I want:

A way or an idea - as you prefer - to filter by "exclusive" options. If I choose option <option value="2,5">FLOW,Bell</otion> it will return all devices that has this SIM combination: 2,5. In this case - with few records - device's id 5and 6 should be returned.

Users may choose

<option value="1">Digicel</otion>
<option value="3">Rogers</otion>
<option value="2,3">FLOW,Rogers</otion>
<option value="2,5">FLOW,Bell</otion>

In this case it should return all devices that has only SIM with operator#1 or SIM with operator#3 or SIMs with operator's #2 AND operator's #3 or SIMs with operator's #2 AND operator's #5:

devices #2,#3,#4,#5,#6.

In the database I created a package with the follow function:

FUNCTION generalMeasurements (
   models             IN   VARCHAR2,
   manufacturers      IN   VARCHAR2,
   idsoperators       IN   VARCHAR2
)
   RETURN sys_refcursor 

IS

vmanufacturers table_string := str2table(manufacturers);
vidmodels table_string := str2table(models);

cout sys_refcursor;

BEGIN
    open cout for
    select count(*), bla bla bla
      from devices inner join operators_device on id = id_device
        inner join operator on id_operator = operator_id
        WHERE (   (models IS NULL)
                  OR id_model IN (
                                         SELECT COLUMN_VALUE
                                           FROM TABLE
                                                     (vidmodels))
                 )
             AND (   (manufacturers IS NULL)
                  OR id_manufacturer IN (
                                            SELECT COLUMN_VALUE
                                              FROM TABLE
                                                        (vmanufacturers))
                 );

END;

CREATE OR REPLACE TYPE table_string IS TABLE OF VARCHAR2(30);

CREATE OR REPLACE FUNCTION str2table (p_str IN VARCHAR2)
   RETURN table_string
IS
   l_str    LONG         DEFAULT p_str || ',';
   l_n      NUMBER;
   l_data   table_string := table_string ();
BEGIN
   LOOP
      l_n := INSTR (l_str, ',');
      EXIT WHEN (NVL (l_n, 0) = 0);
      l_data.EXTEND;
      l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_str, 1, l_n - 1)));
      l_str := SUBSTR (l_str, l_n + 1);
   END LOOP;

   return l_data;
END;

So... any ideas to do that?

Answer 1

I've already added an answer which works correctly, but have since found out that Oracle has an aggregation function for constructing comma-delimited lists.

That makes this query quite simple (and as a bonus uses the IN keyword!):

select  *
from    (
            select  id_device,
                    /* Create comma delimited list of operators for each device */
                    LISTAGG(id_operator, ',') 
                    WITHIN GROUP (ORDER BY id_operator) AS op_list
            from device_operators
            group by ID_device 
        ) As a
 where  op_list in ('1','2,3','2,5','3')

See this SQLfiddle for an equivalent working version in Postgres (Oracle isn't working in SQLfiddle for some reason).

Answer 2

Quite interesting problem..

My approach is

1. test all relations against all requested options
2. identify non matching ones
3. subtract non matching ones from good ones

I assume options passed as a single string in the form:

"opt_val_1;opt_val_2;..opt_val_n"

where each opt_val will be in the form:

"operator_1,operator_2..operator_n"

I wrote it for sql-server, which is the dbms I best know, then I have translated it to ORACLE, I have tested it on Oracle live SQL, but maybe there is a better syntax..

the query should be:

WITH 
-- this is your parameter
OPTIONS AS (
    SELECT '1;3;2,3;2,5' OPT_VAL FROM DUAL
),
-- this is the splitted list of options
opt as (
    select id opt_n, val opt_val 
    from (
        select ROWNUM id, regexp_substr(OPT_VAL,'[^;]+', 1, level) VAL
        from OPTIONS
        connect by regexp_substr(OPT_VAL, '[^;]+', 1, level) is not null
    ) x
),
-- this is the list of devices with relations
dev as (
    select distinct id_device
    from relations
)

-- this is the list of devices exploded by options
select * 
from (
    select o.opt_n opt_n, dr.id_device
    from dev dr
    cross join opt o
) dr

minus 

-- this is the list of invalid devices exploded by options
select * 
from (
    select distinct COALESCE(r.opt_n,o.opt_n) opt_n, COALESCE(o.id_device, r.id_device) id_device
    from (
        select dr.id_device, s1.opt_n opt_n, s1.opt_val, s2.Id dev_n, s2.val id_operator
        from dev dr
        cross join opt s1
        cross apply (
            select *
            from (
                SELECT ROWNUM id, regexp_substr(sx.opt_val,'[^,]+', 1, level) val
                FROM (select s1.opt_val opt_val from dual) sx
                connect by regexp_substr(sx.opt_val, '[^,]+', 1, level) is not null
            ) x
        ) s2
    ) o
    full join (
        select  ID_DEVICE,  ID_OPERATOR, OPT_N, OPT_VAL
        from relations r
        cross join opt o
    ) r
    on (o.id_device=r.id_device)  and (o.opt_n = r.opt_n)  and (o.id_operator = r.id_operator)
    where o.id_device is null or r.id_device is null
) x
order by 1,2

and this is the output:

opt_n   id_device
1       2
2       3
3       4
4       5
4       6

Let me know if this is what you are looking for