SQL join multiple criteria

Question

I have a difficult task to build up an array retrieved from a table similar to the one below:

table_a

id  | scenario_id | entity_id
1     1;2;3;4;5     1;3
2     4;5;8;10      2;3
3     1;5;8;11      1;2;4;
4     3;5;8;9       4;5;

Now, if one user selects from one entity_id, let's say 3, the SQL query should return something similiar to:

scenario_id
1;2;3;4;5;8;10

Or, if he selects 5, the returned array should look like:

scenario_id
3;5;8;9

Could that be done using only SQL statements?

you can do this by creating a procedure ....where a convert all comma separated values into rows ,and then store them in a temporary table.Then after retrieve your desired data from that temp table :) — Ankit Agrawal, Jun 10 '16 at 09:13
Don't do this. have a row for each combination of scenario_id and entity_id. — Rich Benner, Jun 10 '16 at 09:16
@rosuandreimihai - why the query only returns only the row with the id 1 and not the row with the id 4. there is also a 3 in scenario_id — Bernd Buffen, Jun 10 '16 at 09:17
One user can select only one entity_id per time, that's why a gaved that example, but for sure one user could select either of the entities from 1 to 5 — rosuandreimihai, Jun 10 '16 at 09:18
Please only tag the DB you are using. SQL Server and MySQL often require different solutions. Like others here I'd recommend you [normalise](https://en.wikipedia.org/wiki/Database_normalization) your table structure. You'll find it much easier to work with the content when each field in each row contains only 1 scalar value. — David Rushton, Jun 10 '16 at 09:21

score 1 · Answer 1 · answered Jun 10 '16 at 09:20

1

you can use something like this to find a id in the scenario_id, but its always a FULL TABLE scan.

SELECT *
FROM table_a
WHERE
 FIND_IN_SET('3', REPLACE(scenario_id,';',',')) > 0;

answered Jun 10 '16 at 09:20

Bernd Buffen

14,525
2
24
39

Plus, it's a terrible idea. – Strawberry Jun 10 '16 at 09:22

score 1 · Accepted Answer · answered Jun 10 '16 at 09:46

For SQL Server you can use this to get desired output:

DECLARE @xml xml, @entity_id int = 3
--Here I generate data similar to yours
;WITH cte AS (
SELECT *
FROM (VALUES
(1, '1;2;3;4;5', '1;3'),
(2, '4;5;8;10', '2;3'),
(3, '1;5;8;11', '1;2;4;'),
(4, '3;5;8;9', '4;5;')
) as t(id, scenario_id, [entity_id])
)
--create xml
SELECT @xml = (
SELECT CAST('<i id="'+ CAST(id as nvarchar(10)) +'"><s>' + REPLACE(scenario_id,';','</s><s>') + '</s><e>' + REPLACE([entity_id],';','</e><e>') + '</e></i>' as xml)
FROM cte
FOR XML PATH('')
)
--Normalizing the table and getting result
SELECT STUFF((
SELECT ';' + CAST(scenario_id as nvarchar(10))
FROM (
    SELECT DISTINCT t.v.value('.','int') as scenario_id
    FROM @xml.nodes('/i/s') as t(v)
    INNER JOIN  @xml.nodes('/i/e') as s(r)
        ON t.v.value('../@id','int') =  s.r.value('../@id','int')
    WHERE s.r.value('.','int') = @entity_id
) as p
FOR XML PATH('')),1,1,'') as scenario_id

Output for entity_id = 3:

scenario_id
1;2;3;4;5;8;10

For entity_id = 5

scenario_id
3;5;8;9

score 0 · Answer 3 · answered Jun 10 '16 at 09:26

Simple. NORMALISE your schema... At it's crudest, that might be as follows...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id  INT NOT NULL
,scenario_id INT NOT NULL
,entity_id INT NOT NULL
,PRIMARY KEY (id,scenario_id,entity_id)
);

INSERT INTO my_table VALUES
(1, 1,1),
(1, 1,3),
(1, 2,1),
(1, 2,3),
(1, 3,1),
(1, 3,3),
(1, 4,1),
(1, 4,3),
(1, 5,1),
(1, 5,3),
(2, 4,2),
(2, 4,3),
(2, 5,2),
(2, 5,3),
(2, 8,2),
(2, 8,3),
(2,10,2),
(2,10,3),
(3, 1,1),
(3, 1,2),
(3, 1,4),
(3, 5,1),
(3, 5,2),
(3, 5,4),
(3, 8,1),
(3, 8,2),
(3, 8,4),
(3,11,1),
(3,11,2),
(3,11,4),
(4, 3,4),
(4, 3,5),
(4, 5,4),
(4, 5,5),
(4, 8,4),
(4, 8,5),
(4, 9,4),
(4, 9,5);

SELECT DISTINCT scenario_id FROM my_table WHERE entity_id = 3 ORDER BY scenario_id;
+-------------+
| scenario_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           8 |
|          10 |
+-------------+

score 0 · Answer 4 · edited May 23 '17 at 10:33

0

split the scenario_id by ';' and copy to temporary table to use that for your query use instr and substring functions this link may help you but you need a loop function to call your procedure as the ';' is repeated

edited May 23 '17 at 10:33

Community

1
1

answered Jun 10 '16 at 09:47

afzali

125
1
3
12

SQL join multiple criteria

4 Answers4