MySQL select rows where values in one field match values in another table

I have the following tables:

• tbljobpost with fields job_id int, job_title varchar(50), skill_cat_id varchar(50)
• tblappskills with fields app_control Int, skill_cat_id Varchar(50)

tbljobpost:

+-------------------------------------+
| job_id | job_title  | skill_cat_id  |
+-------------------------------------+ 
| 1      | Programmer | 1,2,3,4,5,6,7 | 
+-------------------------------------+ 

tblappski开发者_运维百科lls:

+-----------------------------+ 
| app_control | skill_cat_id  | 
+-----------------------------+ 
| 1           | 1,2,4,5,6     | 
| 2           | 1,2,3,7,4     | 
| 3           | 1,2,3,4,5,6,7 | 
| 4           | 7,1,4,5,6,2,3 |
+-----------------------------+ 

How can I query or filter the tblappskills that is the same skill_cat_id separated with comma from tbljobpost? And the result will come like this:

+-----------------------------+ 
| app_control | skill_cat_id  | 
+-----------------------------+ 
| 3           | 1,2,3,4,5,6,7 | 
| 4           | 7,1,4,5,6,2,3 |
+-----------------------------+

Your solution does not fit first normal form

You should store skill categories in separate cells like this:

+----------------------------+
| app_control | skill_cat_id |
+----------------------------+
| 1           | 1            |
| 1           | 2            |
| 2           | 1            |
| 4           | 4            |
+----------------------------+ 

Then, you can easily JOIN the tables and select the rows matching the value you want. It does not matter that app-control-id appears more than once in the table. However, if you decide to put additional data to that table, you should do it in separate table to avoid redundancy, like this:

Your new table (contains detail information about app-control) is related to the table I have mentioned above as 1 : (0..N). It's hard to explain but easy to design. If you study the normal forms mentioned above, you will understand this easily.