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/ ]