Can anyone recommend a good sql mailing list.
I've got a faily complex query I'm trying to formulate and I need some help.
I'll put the post here if anyone wants to take a crack at it.
Hi,
I have a query that looks like this:
SELECT skill.username, name, pri, COUNT(search.skill.name) AS score
FROM skill
WHERE name IN ('PHP', 'JavaScript') AND pri=1
GROUP BY username
ORDER BY score DESC;
This works nicely for a simple search and ranking, but I would like to modify the 'score' value for each match that COUNT comes up with. That is, I'd like to be able to iterate over each row that COUNT matches and do some conditional alterations to 'score' on the fly.
For instance, if I add this to the COUNT statement:
( COUNT(search.skill.name) + IF(name='JavaScript' AND pri=1, 5, 0) ) AS score
This allows me to add 5 to 'score' if the match is what I'm looking for. I'm hoping to add a weight to all of my matches so that certain types of matches are weighed heavier that others. For example, if 'pri' was equal to 0 instead of 1, then I might add 0 instead of 5.
Problem is: The IF statement in this query only seems to apply to one of the matches that COUNT makes. Looking back to my code above, if the code matched PHP and JavaScript, and if my result contains the PHP match only, then my IF statement has no effect. (even though both JavaScript and PHP were matched)
I would like to be able to iterate over all of the COUNT matches, not just one.
Any hints or help? Perhaps there's a different and better way of doing this sort of weighting?
Many Thanks,
matt
