thanks all, managed to figure it out earlier...and it was just changing one of the clauses around...works fine now :)
On Tue, Aug 17, 2010 at 10:18 AM, Neven MacEwan <[email protected]> wrote: > 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] -- Kind Regards, Brendan Brink SMS Marketing Consultant | Manager Sell2Cell Ltd. 021 0246 1646 | [email protected] | www.sell2cell.co.nz We provide customized, cost-effective SMS & Web Solutions Need a website? Need to integrate text-messaging into your business or website? Contact us today for a free no-obligation quote! VISIT OUR ASSOCIATED WEBSITES: textvouchers.com | textguru.co.nz WARNING This email contains information which is CONFIDENTIAL and may be subject to LEGAL PRIVILEGE. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy the email or attachments. If you have received this in error, please notify us immediately by return email, facsimile, or telephone (call us collect). -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
