MySQL relationship query

Question

I have two tables, the 1st:

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

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

its possible make this in one query ?

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 is: all users who has php 2 AND html 1.

Answer 1

select *
   from users u join skills s on u.id=s.user_id 
   where skill_level=2 
   group by id 
   having count(*)>2

Answer 2

Okay. Now that you've updated your question a bit more, the answer becomes "yes, it can be done, but you shouldn't necessarily do it like that".

Firstly, just to show that it 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;

Would have saved me quite a bit of typing if you'd explained what you wanted in the beginning! :)

Now should you do the above? It's not very pretty, but the principle is that you join to the table twice for both different skills, using aliases (s1 and s2) to hide the fact that it's the same table. Sometimes this is the right approach. The trouble is that I suspect you'll have loads of variations on this where you want to sometimes find people with lots of skills at different levels, sometimes only one, etcetera. You might find writing the code to automatically generate those queries slightly complicated and it wont necessarily scale well.

You need to read up on database normalization to better design your tables. And you should also have an id field for the skills table and then you can more easily use sub-queries when you need to.