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

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

[email protected], 3

[email protected], 5

[email protected], 10

[email protected], 20

[email protected], 3

[email protected], 6

[email protected], 9

[email protected], 3

[email protected], 5

[email protected], 10

[email protected], 20

[email protected], 21

[email protected], 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: [email protected]

jobid: 125

 

username: [email protected]

jobid: 125

 

[email protected] 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` = '[email protected]'

 

 

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.