Select records from a table where one column value lies in another column in the same table using SQL

Question

I want to select rows from a table where a column value lies in another column value of the same table. I am trying to execute code in Oracle SQL developer

I have a larger input dataset similar to below :

keys	val	set
1	H	H, L , M
2	L	P, Q , R
3	P	P, S

I want to select only those records where my val column value lies in the set column of the same table. There can be multiple or single values in the set column The output would be like this :

keys	val
1	H
3	P

How to achieve it using MySQL ? Please help.

**Note **: I have tried using below code. Its not working :

select keys, val from a where val in (set)

(remove oracle tag if you are only using mysql) If the actual data is really as the sample shows, then checking if INSTR(set,val) <> 0 is enough, but if actual data contains words that may be subwords of the comma separated list of values SET then it will not work anymore (you may get false positive), you will need to use regular expression (REGEXP) or normalize the SET column to be able to use a simple WHERE with equality check. — p3consulting, Jan 05 '23 at 12:17
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Jan 05 '23 at 13:27

score 1 · Accepted Answer · answered Jan 05 '23 at 12:20

1

you can do a simple comparison using concat :

SELECT `keys`, val
FROM dataset 
where `set` like concat('%',val,'%');

Try it here : https://dbfiddle.uk/HZ5spY-k

answered Jan 05 '23 at 12:20

SelVazi

10,028
2
13
29

1

Thanks @SelVazi, this is working and a easy to implement solution as well – Shubhi Karamveer Jan 05 '23 at 13:20
1

Note that `like concat('%', 'John' ,'%')` also will return `Johnson` etc. – jarlh Jan 05 '23 at 13:30

score 0 · Answer 2 · answered Jan 05 '23 at 12:12

0

I think it's need something like:

SELECT keys, val FROM a WHERE SET='val'

answered Jan 05 '23 at 12:12

Szabolcs Horvath

79
1
9

1

I think this will work only for single values not for multiple – Shubhi Karamveer Jan 05 '23 at 12:25

score 0 · Answer 3 · edited Jan 05 '23 at 12:21

0

I think you should try this

SELECT `keys`, `val` FROM `temp` WHERE FIND_IN_SET(`val`, `set`);

edited Jan 05 '23 at 12:21

Arulkumar

12,966
14
47
68

answered Jan 05 '23 at 12:16

Praveen Pathak

1
2

this is not working :( – Shubhi Karamveer Jan 05 '23 at 12:22
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 05 '23 at 13:51
@ShubhiKaramveer it's working perfectly you can check here https://dbfiddle.uk/oTSCCgWm – Praveen Pathak Jan 06 '23 at 06:07

Select records from a table where one column value lies in another column in the same table using SQL

3 Answers3