1

See Data stored

How to select rows where value is in array?

SELECT * WHERE find_in_set ('1','2',3',classid)

where class id stored as multiple id like 1,24,5,6

http://sqlfiddle.com/#!9/cbac415/2

SELECT * WHERE find_in_set ('1',2','3',classid)

Which doesn't work. Is there another way to do this? where classid stored as multiple ids

Fighter
  • 69
  • 3
  • Seems you might want something like `SELECT * WHERE find_in_set IN ('1',2','3',classid)` ? – Gekkie Apr 24 '19 at 11:31
  • 1
    You can use multiple `find_in_set`s with AND/OR – Vatev Apr 24 '19 at 11:34
  • @Gekkie this is not working – Fighter Apr 24 '19 at 11:39
  • @vatev give an example – Fighter Apr 24 '19 at 11:39
  • use IN keyword for multiple where. Also you can use a query for getting group. like : 'Select * from where id IN (select id from y)' – Mert Akkanat Apr 24 '19 at 11:40
  • @MertAkkanat this is not working – Fighter Apr 24 '19 at 11:44
  • 2
    You should definitely look up database normalization. Everything else will only hurt – Nico Haase Apr 24 '19 at 12:10
  • @NicoHaase this is not duplicate .. this question is different from others – Fighter Apr 24 '19 at 12:14
  • Can you explain that? Have you tried to solve your problem using the linked answer? What makes the difference? – Nico Haase Apr 24 '19 at 12:16
  • I have already tried. see table row containing multiple ids. And searching query has an array . like find_in_set('1,2',tablerow) – Fighter Apr 24 '19 at 12:19
  • i also advice you reading [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) .. *"Which doesn't work. Is there another way to do this? where classid stored as multiple ids "* Yes that approach is called [Normalization](https://en.wikipedia.org/wiki/Database_normalization) – Raymond Nijland Apr 24 '19 at 12:23
  • Well there is a other approach if you can't normalize if this is a existing running production application database.. You can parse out delimited list to records but it's is [complex](http://sqlfiddle.com/#!9/cbac415/10) then you can use anny SQL operator on those records as normal.. – Raymond Nijland Apr 24 '19 at 12:36

1 Answers1

0
SELECT *
FROM my_table
WHERE (classid1, classid2, classid3, classidn) 
IN ((1, 2, 3, 4), ... ,(2, 6, 9, 12));
crafter
  • 6,246
  • 1
  • 34
  • 46