mysql join with a count or ?! help please

[esas_nefret]

Hi,

 

I am working for a company. We have launched the website but it requires more editing on the system.

 

System is a platform that matches the jobs with the members' skills. When adding jobs, admin chooses the jobs skills.. When a member registers, member also chooses their skills. Then a cron system matches the jobs and members, and inserts the result to a temporary database. Another cron runs and sends email to members from time to time.

 

Members' skills table:

 

username , skill

--------------------

aa@aa.com, 3

aa@aa.com, 5

aa@aa.com, 10

aa@aa.com, 20

bbbb@bb.com, 3

bbbb@bb.com, 6

bbbb@bb.com, 9

cc@cc.com, 3

cc@cc.com, 5

cc@cc.com, 10

cc@cc.com, 20

cc@cc.com, 21

cc@cc.com, 22

 

 

Jobs' skills table:

 

jobid, skill

----------------------

125, 3

125, 5

125, 10

125, 20

200, 3

200, 6

200, 9

 

I specified a limit for matching. Both users and jobs skills should be matched if the total count over 4 or equal to 4. ( >= )

 

For example, the result of the table above should be:

username: aa@aa.com

jobid: 125

 

username: cc@cc.com

jobid: 125

 

bbbb@bb.com should be in the result because the jobid which is 200's total count is 3. Skills are same but it is not equal to the limit that I specified which is 4 or above.

 

 

Here is the code that I use:

 

SELECT distinct(jobid)
FROM `jobs_skills`
INNER JOIN `members_skills` AS `members_skills` ON ( `members_skills`.`skill` = `jobs_skills`.`skill` )
WHERE `members_skills`.`username` = 'aa@aa.com'

 

 

The above code works fine but matches everybody even if they have 1 skill..

 

 

Please help me....

[o3d]

You need a column in one table to link the other table or a 3rd table to link the two tables. The only relationship between the two tables currently is the skill level.