-1

I've seen a lot of questions with a similar question. However, all the answers are misleading. Most answers say to use FIND_IN_SET and this seems incorrect to my question.

Below query:

SELECT 
  * 
FROM
  data_table 
WHERE field_id IN ('618,622,626,773,776');

Would normally just show the first ID row. In this example being '618'

id   data 
---  ------
618  ....

How would one go about this without using any stored procedures or user defined functions? Displaying the following results:

id   data 
---  ------
618  ....
622  ....
626  ....
773  ....
776  ....

Note; '618,622,626,773,776' will always remain as a string. This is the problem here.

tfont
  • 10,891
  • 7
  • 56
  • 52
  • 3
    Why is `FIND_IN_SET` "completely incorrect" here? It looks for an entry in a string that contains entries comma-separated. Isn't this exactly what you want? `IN` on the other hand looks for an entry in a data set, but you don't have a data set, you have a string. – Thorsten Kettner Nov 03 '16 at 11:50
  • 3
    FIND_IN_SET __is__ the correct answer. `SELECT * FROM foo WHERE FIND_IN_SET(field_id, '618,622,626,773,776')` – CBroe Nov 03 '16 at 11:51
  • A side note: That your query finds the record with field_id 618 is a flaw in the DBMS in my opinion. '618,622,626,773,776' should not be convertable to int, but MySQL simply converts the first substring. – Thorsten Kettner Nov 03 '16 at 11:53
  • Hello. Those set of numbers are coming from php? – L. Herrera Nov 03 '16 at 11:54
  • Seems like you don't need the quotes, but I'm unclear about your note that it will always be a string. The form `WHERE field_id IN (618,622,626,773,776)` should work – Jeff Puckett Nov 03 '16 at 11:57

1 Answers1

4

Most answers say to use FIND_IN_SET and this is completely incorrect to my question.

It is completely correct. If it didn’t work for you – then you just used it wrong.

SELECT * FROM data_table WHERE FIND_IN_SET(field_id, '618,622,626,773,776')
CBroe
  • 91,630
  • 14
  • 92
  • 150
  • Thank you! Maybe I was a bit too forward. But with a better applied example, I see what you mean. – tfont Nov 03 '16 at 14:17