Query with FIND_IN_SET and array

Question

I have two tables tbl_user and tbl_projects

tbl_user
+-----+------+--------+
| id  | name | skills |
+-----+------+--------+
|  u1 | x    | s1,s2  |
|  u2 | y    | s2,s3  |
|  u3 | z    | s3,s1  |
+-----+------+--------+

tbl_projects
+-----+--------+
| id  | kills  |
+-----+--------+
|  p1 | s2     |
|  p2 | s1,s3  |
|  p3 | s3     |
+-----+--------+

For my application I want a sql query for list out all projects those which are match with the user skill

example, If i select the user u1 the result will be like

+-----+--------+
| id  | kills  |
+-----+--------+
|  p1 | s2     |
|  p2 | s1,s3  |
+-----+--------+

The architecture is wrong for matching, You need to normalize `skills` as a different table and have association with `user` and `project`. That way, it will be easy to filter. — Jeet, Aug 03 '16 at 11:44

BeetleJuice · Accepted Answer · 2016-08-03T12:48:46.937

People better than I can help you build an SQL query to get you there. The way your tables look though, you will always have to build complex queries to pull out useful information. My advice, especially if you are still early in your project, is to change your table structure to make querying easier.

For instance, the tables below reflect the info in your OP in a structure that will make your life much much easier.

tbl_users: holds details that have a one-to-one relationship with each user

userID|name|email...
 u1   |  x | ...
 u2   |  y | ...
 u3   |  z | ...

tbl_skills: details that have a one-to-one relationship with each skill

skillID
s1
s2
s3

tbl_projects: details that have a one-to-one relationship with each project

pID|   title   | deadline
p1 | project a | 2016-08-15
p2 | project b | 2017-01-01
p3 | project c | 2015-08-22

tbl_user_skills: Each record has one user and one skill, both of which are foreign keys to this table (primary keys in tbl_users and tbl_skills respectively). It should have a UNIQUE index on (userID,skillID) to prevent duplicate entries.

userID|skillID
 u1   | s1
 u1   | s2
 u2   | s2
 u2   | s3
 u3   | s1
 u3   | s3

tbl_project_skills Each record has one project and one skill, both of which are foreign keys to this table (primary keys in tbl_project and tbl_skills respectively). It should have a UNIQUE index on (pID,skillID) to prevent duplicate entries.

pID|skillID
p1 |s2
p2 |s1
p2 |s3
p3 |s3

Once everything is organized this way, your queries will be much faster and much simpler to build. In fact, if you understand bitflag operations, you could condense this considerably (eg: have all skills from a user as one field in tbl_users but instead of s1,s2 you would use bits).

To get all projects with the skills of user u1:

SELECT p.pID, p.title
FROM tbl_projects p
LEFT JOIN tbl_project_skills ps ON p.pID = ps.pID
LEFT JOIN tbl_user_skills us ON ps.skillID = us.skillID
WHERE us.userID='u1'
GROUP BY p.pID

Result

pID| title
p1 | project a
p2 | project b

@Strawberry None that answers the specific question in the OP, but I always have a primary key in every table I build because I want each record in each table to be uniquely identifiable. It's harder to add a primary key later, if your application comes to need it. — BeetleJuice, Aug 03 '16 at 12:11
You have a perfectly good natural key in both of those tables. There's no need for a surrogate. — Strawberry, Aug 03 '16 at 12:18
@BeetleJuice, the two last tables should a UNIQUE index. One on userID|skillID and one on pID|skillID. Having those indexes makes each row "uniquely identifiable" as you want it. Current id column is completely redundant. — alex smith, Aug 03 '16 at 12:45
@Strawberry thank you for the feedback. I'll read up on the pros and cons. — BeetleJuice, Aug 03 '16 at 12:46
@alexsmith I agree fully. I've added that note to the answer — BeetleJuice, Aug 03 '16 at 12:46
@BeetleJuice Thank you for your response. But it is a existing project. I can't change the DB structure. Only I can do the php coding. What can I do for the same? — Croos Nilukshan, Aug 03 '16 at 14:20

Query with FIND_IN_SET and array

1 Answers1