Sorry but this is BS, the query is relatively straightforward, either with
subselects, or a straight group by, The issue is the limitaions of MYSQL's
query engine
The basic query is
SELECT
es2.employee_id, cs2.company_id, count(*) as skills_match
FROM
employees_personal e JOIN
employees_skills es2 on es2.employee_id = e.id JOIN
company_skill cs2 on cs2.skill = es2.skill
WHERE cs2.company_id = '117' AND 1
GROUP BY es2.employee_id, cs2.company_id
HAVING count(*) > ?
ORDER BY skillswanted DESC
you can add other clauses in the order by by adding MAX(coulmnname) if its
in the emp or co table
> I believe you will have a very hard time doing this in straight SQL, as
> it is not flexible enough. The 'number of matches' part is particularly
> difficult. If mysql supports user-defined functions as MSSQL Server
> does, you might have a shot at it...
>
> But I would try to use an approach that does more of the logic in PHP.
> Use SQL to get a set of employees which have *any* skill in common with
> what the company wants, then use PHP to handle the prioritisation. You
> may need to do subsequent lookups for each employee to find out how many
> skills there are in common, but this may not be a performance issue
> depending on how many records you expect there to be.
>
> R
>
> On 17/08/10 09:17, Brendan Brink wrote:
>> hi there,
>>
>> have a query with building an SQL query...
>>
>> SELECT DISTINCT(e.id),cs2.company_id as companyid, count(es2.skill) as
>> skillswanted FROM employees_personal e INNER JOIN employees_skills es2
>> on es2.employee_id = e.id INNER JOIN company_skill cs2 on cs2.skill =
>> es2.skill WHERE cs2.company_id = '117' AND 1 GROUP BY companyid order
>> by skillswanted DESC, e.first_name,e.last_name
>>
>> Basically...
>>
>> Employees_personal have skills in the employees_skills table,
>> Companies have skills they require in company_skill table
>>
>> i want to find all employees who have skills that match skills in the
>> company skill table, and then order by the number of matches desc to
>> find most suitable employee.
>>
>>
>>
>> ... currently the query is not giving any errors, but it only returns
>> one employee, when I know there should be alot more results...
>>
>> also...ideally would like to create a HAVING clause on the number of
>> skillswanted...so can say only display employees who match at least 2
>> skills.
>>
>> thanks..for any help would be appreciated...
>>
>>
>
> --
> Phone (04) 381 4827 or 021 823 129
> Skype rimu123
> Web http://rimu.geek.nz/
>
> --
> NZ PHP Users Group: http://groups.google.com/group/nzphpug
> To post, send email to [email protected]
> To unsubscribe, send email to
> [email protected]
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
