Hi Nancy, David, Ivan, Robbie, Rob, Jason
Thanks for wonderful ideas in this regard.
Probably three different kind of query, which will work in this case as discussed above.
1. GROUP BY .. HAVING COUNT(*)
2. EXISTS
3. And the one suggested by Ivan.
I adopted the below query, though not sure which one is faster in MySQL.
SELECT EmployeeID FROM `table`
WHERE `skill` IN ('PHP','Java')
GROUP BY EmployeeID
HAVING COUNT(*) = 2
As Nancy suggested, parametrized it as required.
If the query would have been "search the employees having mandatory skills PHP, Java and Optional skills Pearl, VoIP".
I want to put it in single SQL query. Please suggest me what should be query.
And the result should be shown in such a way that, people having more number of optional skills should be shown at start.
How can we acheive it?
Best Regards
Austin
On Sun, Apr 17, 2011 at 5:57 AM, nan wich
<nan_wich@bellsouth.net> wrote:
I apologize, Ivan, in a quick read, I apparently confused your method with an earlier method. Indeed the multiple-join-same-table method not only works, but is the one often found in core (e.g. Taxonomy.module). I have not benchmarked either one, but I am going to assume that some core developer has at some time and that whoever that was discovered that either it is faster or that it is the best cross-database implementation. I do, however, feel that the EXISTS method is a more elegant and straight-forward solution, at least in MySql.
Nancy
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
From: Ivan Sergio Borgonovo
> Actually, Ivan's query won't work because each skill is on a
It happens it worked with real data on a real DB (no pun on
MySQL)
--
[ Drupal support list | http://lists.drupal.org/ ]