Would you mind posting your correction so that everyone may benefit? On Aug 17, 12:04 pm, Brendan Brink <[email protected]> wrote: > 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 > >> Webhttp://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]
