MySql determine all elements of string list exists in other string list

Question

I have these tables:

category_table
+--------+-----------------+---------+
| id     | title         | parent_id |
+--------+---------------+-----------+
| 1      | f             | null      |
| 2      | h             | null      |
| 3      | j             | 1         |
| 4      | y             | 3         |
| 5      | d             | 3         |
+--------+-----------------+---------+

post_table
+--------+---------------+-------------+
| id     | title         | categoryies |
+--------+---------------+-------------+
| 1      | title1        | 1           |
| 2      | title2        | 1,2         |
| 3      | title3        | 4,5         |
| 4      | title4        | 4           |
| 5      | title5        | 3           |
+--------+---------------+-------------+

and a procedure that find all posts that has '3,5,2' categories.

how procedure will search string list in other string list similar to:

Create Procedure post_select(IN category_ids Varchar(200))
Begin
   Select *
   From post
   Where 
   // all category_ids exists in post categories column
   // such as FIND_IN_SET('1', '1,2,3') but first parameter
   // should be list string for example:
   // FIND_IN_SET('2,3,4','2,4,5,6,7,8')

End;

@Gunaseelan Who cares? Until the schema is fixed, there's no point 'trying' anything! — Strawberry
– Strawberry, Commented Oct 25, 2017 at 12:43
This question needs some clarification (and some typo fixing too). Do you want the procedure to receive a parameter with a comma-separated string that's interpreted as a list of values, of which any will make the row appear in the result? Also it's helpful to demonstrate the desired result, to cross-check if we understand it right. — dkellner
– dkellner, Commented Oct 25, 2017 at 12:46

Strawberry · Accepted Answer · 2017-10-25 12:55:34Z

1

You should consider creating a post_category table where you store the post_id together with the category_id.

edited Oct 25, 2017 at 12:55

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Oct 25, 2017 at 12:37

Sonny Hansen

6301 gold badge6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SLDev Over a year ago

I need comma separated search but don't have any function for searching list in other list ...

Strawberry Over a year ago

@SLDev If you need that, don't bother using an RDBMS.

Strawberry Over a year ago

@SLDev The point is simple. Either normalise your schema, or don't use a relational database management system; there's nothing wrong with using comma separated lists, and there's nothing wrong with using relational databases; just don't try to use both together.

Collectives™ on Stack Overflow

MySql determine all elements of string list exists in other string list

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related