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