CODEWITHSUNDEEP
mysqlsql
Tim C
Tim C

Reputation: 5714

mysql return zero to many results and closest result based on condition

With the above information in mind consider the following table:

enter image description here

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

Answers (2)

jeroen
jeroen

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

Gordon Linoff
Gordon Linoff

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

Related Questions