Mysql – Search Across Multiple Rows

MySQL

I have a database that is full of doping results for athletes.

+-----------+------+----------+
| athleteId | test | outcome  |
+-----------+------+----------+
|    100011 |    1 | Pending  |
|    100012 |    1 | Positive |
|    100012 |    2 | Negative |
|    100013 |    2 | Positive |
|    100013 |    3 | Negative |
|    100014 |    2 | Positive |
|    100022 |    1 | Negative |
|    100022 |    2 | Positive |
|    100022 |    3 | Positive |
|    100023 |    2 | Negative |
|    100030 |    1 | Negative |
|    100031 |    2 | Disputed |
|    100033 |    4 | Pending  |
|    100036 |    5 | Positive |
|    100037 |    5 | Positive |
|    100038 |    5 | Positive |
|    100051 |    1 | Pending  |
|    100054 |    1 | Positive |
+-----------+------+----------+

What I'm needing to return is the athleteId where a test returned a positive, and then another test returned a negative.
That narrows it down to athleteIds 100012, 100013, 100022, but I'm not sure how to structure a query to return this.

Any help is greatly appreciated!

Best Answer

Assuming the test numbers suggest the order in which the tests are taken and you want athletes having a Negative test coming after the last Positive test of theirs, you can group your table by athleteId and then:

  • get the last test number where the outcome is 'Negative':

    MAX(CASE outcome WHEN 'Negative' THEN test END)

  • get the last test number where the outcome is 'Positive':

    MAX(CASE outcome WHEN 'Positive' THEN test END)

  • compare the two results to see if the former is greater than the latter:

    HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)

Full query:

SELECT
  athleteId
FROM
  aTable
GROUP BY
  athleteId
HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)
;

If you want athletes who have a Negative test coming after at least one Positive test (not necessarily the last one), you could try a different approach:

  • get a Negative test;

  • see if there exists an earlier Positive test for the same athlete.

Or like this in SQL:

SELECT DISTINCT
  athleteId
FROM
  aTable AS neg
WHERE
  outcome = 'Negative'
  AND EXISTS
  (
    SELECT
      *
    FROM
      aTable AS pos
    WHERE
      pos.athleteId = neg.athleteId
      AND pos.outcome = 'Positive'
      AND pos.test < neg.test
  )
;
Related Question