[support] [development] SQL Query Doubt

Austin Einter austin.einter at gmail.com
Sun Apr 17 09:22:26 UTC 2011


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 at 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/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110417/b22e6343/attachment.html 


More information about the support mailing list