Geek Answers Handbook

MySQL relationship request

I have two tables, the first one:

users(id,name, birth_date)
skills(user_id,skill_name,skill_level)

I want to select all users with 3 skills at level 2.

is it possible to do this in one request?

Example:

user has
3,marcus,19/03/1989
4,anderson,08/02/1990

skills has
3,php,2
3,html,1
4,php,1

what i want: all users who have php 2 AND html 1.

+5
source share
3 answers
select *
   from users u join skills s on u.id=s.user_id 
   where skill_level=2 
   group by id 
   having count(*)>2
+5
source

Good. Now that you have updated your question a little, the answer is "yes, it can be done, but you do not have to do it like that."

First, just to show that this can be done:

SELECT u.* FROM users u 
INNER JOIN skills s1 ON (u.id = s1.user_id AND s1.skill_name = 'php') 
INNER JOIN skills s2 ON (u.id = s2.user_id AND s2.skill_name = 'html') 
WHERE s1.skill_level = 2 AND s2.skill_level = 1 GROUP BY u.id;

I could not type a little text if you explained what you wanted in the beginning! :)

? , , , (s1 s2), , . . , , , , .. , , .

, . id , , .

+1

, 1 PHP 2:

SELECT u.name
FROM users u JOIN skills s1 ON u.id = s1.user_id AND s1.skill = 'php'
JOIN skills s2 ON u.ud = s2.user_id AND s2.skill = 'html'
WHERE s1.skill_level = 2
AND s2.skill_level = 1

If you want all users with HTML and PHP to completely refuse the WHERE clause. If you want all users with a PHP skill of at least 2, you simply change the sentence to

s1.skill_level >= 2
0
source

More articles:


All Articles