0

I have two tables: tbl_post and tbl_tags.

In tbl_post, the field tags contains IDs from tbl_tags as an array. Example: '1,3,15,20' or '4,15,6,21'

I want to select records from tbl_post whose field tags contains an ID from tbl_tags (Example: '15').

How can I do this?

Manh Le
  • 15
  • 1
  • 3

2 Answers2

1

working query for all cases :

SELECT * FROM `tbl_post` WHERE find_in_set('15',tbl_post.tags)

or 

SELECT * FROM `tbl_post` WHERE find_in_set('15',tbl_post.tags) <> 0
Satyendra Yadav
  • 122
  • 4
0

What about: 

SELECT * FROM tbl_post WHERE tbl_post.tags LIKE '%15%';

OR

SELECT * FROM tbl_post WHERE Contains(tbl_post.tags, '15');

As per your comment, you could try this 

DECLARE @id INT = 15
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM tbl_post 
WHERE tbl_post.tags = @stringId -- When there is only 1 id in the table
OR tbl_post.tags LIKE @stringId + ',%' -- When the id is the first one
OR tbl_post.tags LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR tbl_post.tags LIKE '%,' + @stringId -- When the id is at the end

Referenced from this SO post

Community
  • 1
  • 1
ske57
  • 577
  • 4
  • 21
  • what if `tags` is `1,3,154`? – Blorgbeard Oct 28 '16 at 02:48
  • @Blorgbeard I've update the answer to handle this, but really you need a very good reason for using a using a comma separated list value over an efficiently designed DB Schema. – ske57 Oct 28 '16 at 03:02
  • Oh, I agree. I'm not OP, by the way. Just noticed a bug. You could also do something like `where (',' + tbl_post.tags + ',') like '%,15,%'` instead of the three separate clauses. – Blorgbeard Oct 28 '16 at 03:17
  • This is non-standard SQL. Please mention the DBMS for which this is working (the question so far is not marked with any specific DBMS product) –  Oct 28 '16 at 05:51
  • Hello bros., I'm using MySQL. – Manh Le Oct 29 '16 at 03:08