Reputation: 5714
With the above information in mind consider the following table:
Suppose the match result is:
Arsenal 3 Liverpool 2
Result = Arsenal By 1
Im now looking for a way to return all members who guessed the correct result and score difference IF NO (zero) member guessed correctly return members who guessed closest to the correct result
In above Example:
John, Stephan should be returned who guessed closest with Arsenal by 2
With the above in mind I created the following script:
select * from picks where event_id = '$eventId[$key]' and
abs(score-$winScore) = (select min(abs(score-$winScore)) from picks
where pick = '$winner')
The script above is partially working however when more than one member should be returned it gives thr wrong result, and also only returns one member, also when no member picked correct Im getting wrong results.
Any idea or help on how I can tackle/solve my problem?
Upvotes: 0
Views: 52
Reputation: 91792
You have an error in your query: In the inner query you are not selecting the correct event_id
so you will search over all your events, giving you the wrong results:
select * from picks where event_id = '$eventId[$key]' and
abs(score-$winScore) = (select min(abs(score-$winScore)) from picks
where pick = '$winner' AND event_id = '$eventId[$key]')
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ here
Edit: Note that the same applies to the winner in the outer condition.
Upvotes: 2
Reputation: 1271231
One possibility is that you have a limit 1
in the query you are actually running. Or, you are only fetching the first row from the result set.
However, the query also needs to be fixed. You need the event id in the subquery. This isn't obvious because your sample data does not include this column. Also, the winners need to match both in the subquery and the outer query:
select p.*
from picks p
where p.event_id = '$eventId[$key]' and
p.pick = '$winner' and
abs(p.score-$winScore) = (select min(abs(p2.score-$winScore))
from picks p2
where p2.pick = p.pick and
p2.event_id = p.event_id
);
Here is an example in SQL Fiddle of it returning multiple rows. I am a bit confused about the scorediff
in the sample data versus the score
in the query, so you might still need to play with the query to get it to work with your actual data. But the idea is that the subquery needs to be correlated with the outer query to get the right event and winner.
Upvotes: 2