Ahh, 'group by'. I always forget that.
Try something like
SELECT distinct(c.id) as companyid, c.name, count(es.skill) as skillswanted
FROM employees_personal e INNER JOIN employees_skills es ON e.id =
es.employee_id
INNER JOIN company_skill cs ON es.skill = cs.skill
INNER JOIN companies c ON cs.company_id = c.id
WHERE e.id = "1" group by companyid order by
skillswanted DESC limit 3
Personally I would not put myself through this pain to 'improve' a query
that already works, but hey whatever :)
R
On 17/08/10 10:00, Brendan Brink wrote:
thanks for that,
but the query has been done before using the following old style code:
SELECT distinct(c.id) as companyid, c.name, count(es.skill) as skillswanted
FROM employees_personal e, companies c, company_skill cs, employees_skills es
WHERE e.id = es.employee_id
AND es.skill = cs.skill
AND cs.company_id = c.id and e.id = "1" group by companyid order by
skillswanted DESC limit 3
so should be easy to do it using the new style with inner join, just
need some assistance from someone who can see what I am doing wrong...
On Tue, Aug 17, 2010 at 9:45 AM, Rimu Atkinson<[email protected]> wrote:
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]
--
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]
