0

I asked a simpler version of this question at date and miles when over 100, otherwise return red traffic light and then realized I oversimplified my query.

I asked a new question because a solution was provided to my original question, but I asked the wrong question.

Here's the jist:

I had the silly goal of riding a century on each bike this year.

I want to create a status dashboard that has each bike with an icon indicating it's been done, green for yes, red for no.

Additionally, if it is yes, I want to populate the ride_date and ride_miles.

The second criteria is easy enough to do, the SQL query I have is:

SELECT
r.bike_name,
r.ride_date,
r.ride_miles
FROM rides r
JOIN bikes b
  on r.bike_name=b.bike_name
WHERE DATE_FORMAT(r.ride_date, '%Y')='$current_year' and r.ride_user = '$athlete' and b.bike_retired = 'No' AND ride_miles > 100

I experimented with the JOINS of my other question and adding my where clause to the join. They worked good if I was the only user in the table and I didn't have to check if the bike was retired. Once I added those portions it got all messed up and once again I'm lost.

Once I can get the right data back I can use PHP to massage it. I keep thinking I need an ORDER BY.

Community
  • 1
  • 1
sspeed
  • 19
  • 3

1 Answers1

0

Use IF to return a different color depending on whether the threshold has been met.

SELECT r.bike_name, r.ride_date, r.ride_miles,
       IF(r.ride_miles > 100, 'green', 'red') AS color
FROM rides AS r
JOIN bikes AS b ON r.bike_name = b.bike_name
WHERE YEAR(r.ride_date) = $current_year
  AND r.ride_user = '$athlete'
  AND b.bike_retired = 'No'
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • That's kind of cool, I had no idea you could do that. The data is probably sortable, but I was hoping more (again my bad explaining), to come back with the longest miles on each bike, and then I could sort that data out easier. I think with this the data is all there, but it's a full table scan. – sspeed Mar 19 '14 at 17:17
  • Your question didn't make it clear that there are multiple rows and you need to find the one with the max. See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – Barmar Mar 19 '14 at 17:19
  • You should post your schema, sample data, and the result you're trying to achieve, that will make things clearer. A sqlfiddle is also helpful. – Barmar Mar 19 '14 at 17:20