How to use IN as exactly matching SQL?

Question

I have a system that controls phones. As we all know, phones can have one sim or two. It is known that there are different operators. Therefore, I want to show a filter with all possible combinations of telephones and operators. Today I have a universe of 10 thousand devices. At the end, the system shows device measurements, but the user can filter statistics by phone ( apple , samsung , nokia , etc.), Model and .

So, I have this form that will display all combinations currently using the device’s universe.

At the end, I have a filter as follows:

<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 a basic device table (I am going to put only columns that are “important”):

 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 perform some measurements - it doesn’t matter now - and, of course, these are fake ips.

And I have this staging 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 method or idea - as you prefer - is filtered by the "exclusive" options. If I select the <option value="2,5">FLOW,Bell</otion> , it will return all devices that have this SIM combination: 2,5 . In this case - with multiple entries - device identifier 5 and 6 .

Users can 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, he must return all devices that have only a SIM card with operator No. 1 or SIM with operator No. 3 or SIM cards with operator No. 2 And operator No. 3 or SIM cards with operator No. 2 And operator No. 5 :

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

In the database, I created a package using 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 for this?

Answer 1

I already added an answer that works correctly, but I have since found out that Oracle has an aggregation function to create comma-separated lists.

This makes this query pretty simple (and the keyword IN ! Is used as a bonus):

 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 for some reason does not work in SQLfiddle).

Answer 2

For this answer, I suggested that you can get the selected options into a temporary table of some type - I named the selected table with the column name id_list .

My approach is to make a wide join in a staging table and a temporary table. This allows you to get the total number of rows with the corresponding entries.

Then it’s just a matter of counting the number of matching rows and ensuring that it matches:

• The number of elements in the selected parameter and
• The number of operators associated with the device

The attached sqlfiddle uses sqlite, since the oracle version is not working properly. This should be easily translatable in oracle, although I think the only thing you need to do is switch the order of the first two parameters in the replace function.

 select b.id_device, num_device_records, num_id_records, id_list, count(*) as num_occurrences from ( select id_list, /* We need the number of ids that appear in each list */ length( id_list ) - length( replace( id_list, ',', '' ) ) + 1 as num_id_records from selected ) as a inner join device_operators as b /* Join the two tables on records where the id_operator can be found in the id_list */ /* Note that I have added a comma to the beginning and end of each to "anchor" the search */ on ','|| a.id_list ||',' like '%,'||b.id_operator||',%' left join ( /* We also need to total number of times each device appears */ select id_device, count(*) as num_device_records from device_operators group by id_device ) as c on b.id_device = c.id_device group by b.id_device, num_device_records, id_list /* We only want records where the aggregated number of device records is equal to both the number of ids in the list and the number of occurrences of that device in the device_operators table */ having num_device_records = num_id_records and num_device_records = num_occurrences ; 

Answer 3

Pretty interesting problem.

My approach

• check all relationships for all requested parameters.
• identify inappropriate
• subtract inappropriate from good

I assume the parameters are passed as one line in the form:

 "opt_val_1;opt_val_2;..opt_val_n" 

where each opt_val will look like:

 "operator_1,operator_2..operator_n" 

I wrote it for the sql server that dbms knows best, then I converted it to ORACLE, I tested it on Oracle live SQL, but maybe there is a better syntax.

The request 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 result:

 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.

Answer 4

I did not read the question before the OP edited it, so this is my solution, as I understand it.

First of all, always work with sorted pairs for the selected filters. Thus, we always need to compare with two values. When the filter criteria consists of only one statement, reuse the value selected for composing tupla. The parameter value field should look like this:

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

Then we only need to group and select what we need using the filter selected by the user.

 select * from (select intermediateTable.idDevice, min(intermediateTable.idOperator) idOperator1, max(intermediateTable.idOperator) idOperator2 from intermediateTable -- Consider apply filter clause over idDevice when exists filters specified by the user group by intermediateTable.idDevice) where (idOperator1 = firstUserSelectCriterionLowerElement and idOperator2 = firstUserSelectCriterionUpperElement) or (idOperator1 = secondUserSelectCriterionLowerElement and idOperator2 = secondUserSelectCriterionUpperElement) or (idOperator1 = thirdUserSelectCriterionLowerElement and idOperator2 = thirdUserSelectCriterionUpperElement) ..... or (idOperator1 = nthUserSelectCriterionLowerElement and idOperator2 = thdUserSelectCriterionUpperElement) 

If the criterion of the operator is not selected, do not apply an external query where there are suggestions. Using the example suggested by OP, where the user selects:

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

The request should be structured as follows:

 select * from (select intermediateTable.idDevice, min(intermediateTable.idOperator) idOperator1, max(intermediateTable.idOperator) idOperator2 from intermediateTable group by intermediateTable.idDevice) where (idOperator1 = 1 and idOperator2 = 1) or (idOperator1 = 3 and idOperator2 = 3) or (idOperator1 = 2 and idOperator2 = 3) or (idOperator1 = 2 and idOperator2 = 5) 

Hope this helps

Answer 5

Oracle has a good IN function, it can compare lists with a list of lists. Thus, the client side converts user input into table form (or uses server-side partitioning).

 CREATE TABLE device_operators (id_device int, id_operator int) ; INSERT INTO device_operators (id_device, id_operator) VALUES (1, 1), (1, 2), (2, 1), (3, 3), (4, 2), (4, 3), (5, 2), (5, 5), (6, 2), (6, 5) ; CREATE TABLE selected (op1 int, op2 int) ; 

Simulates user input.

 INSERT INTO selected (op1,op2) VALUES (1, null), (3, null), (2,3), (2,5) ; 

Request

 select t.id_device from (select id_device , min(id_operator) op1 , max(id_operator) op2 from device_operators group by id_device ) t join selected s on (t.op1, t.op2) in ((s.op1, coalesce(s.op2, s.op1))) ; 

Answer 6

Sorry for this short answer, but this choice will make it

 select i1.id_device from intermediate i1 where i1.id_operator = 2 and EXISTS ( select * from intermediate i2 where i2.id_operator = 5 and i2.id_device = i1.id_device) 

You can also use INTERSECT (I cannot try right now, but there will be something like this ...

 select id_device from intermediate where id_operator = 2 INTERSECT select id_device from intermediate where id_operator = 5 

Sorry, but I’m in a hurry and can’t explain it better now.

EDIT: I see that you are very interested in using IN to solve this issue, but IN is not exclusive, anyway, using IN, you can do something like ...

 select id_device from intermediate where id_operator in (2, 5) group by id_device having count(*) = 2 

Oh, and you have duplicate lines ... well, maybe something like ...

 select id_device from ( select distinct * from intermediate where id_operator in (2, 5) ) group by id_device having count(*) = 2 

Anyway, I think it's a good idea to get rid of duplicate entries.

Answer 7

If you want to select identifiers that have a precisely defined set, you can do:

 select id from t group by id having sum(case when id_name in (10, 60) then 1 else 0 end)) = 2 and count(*) = 2; 

If two lines can have exact duplicates, use:

 having sum(case when id_name = 10 then 1 else 0 end) > 0 and sum(case when id_name = 60 then 1 else 0 end) > 0 and count(distinct id_name) = 2 

You can get the corresponding rows in several ways. One way uses in :

 select t.* from t where t.id in (select id from t group by id having sum(case when id_name in (10, 60) then 1 else 0 end)) = 2 and count(*) = 2 );