0

This should be simple, and I started out thinking it was, but now I'm at an impasse.

The background, I have a DB with a table where I store my current bikes. I also have a table where I store my current rides. I usually join the two tables on the bike_name column as you'll see in my query below.

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

  1. 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.

  2. 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 ride_miles > 100

The problem with this is it will only pull back rides (and the bikes) over 100. I can massage the data to make a red icon and post the relevant info. I can't populate the red icon for ones without 100 because I won't even know about them with my query. I've been sitting here trying to think about this and can't come up with a good solution. I just started messing with PHP this year, and while I've accomplished a lot, I think sometimes I might do it in convoluted ways.

The thought I had was that I could rewrite the SQL to ORDER BY longest ride for each bike, get that data, then use logic within PHP to populate my info... Hmm, maybe that's what I need to do...

talegna
  • 2,407
  • 2
  • 19
  • 22
sspeed
  • 19
  • 3
  • 1
    Check the case-feature for pinting true or false to your select statement and remove the `ride_miles > 100` part from where clause – frlan Mar 19 '14 at 16:25
  • make it a right join on rides to bikes so all bikes are included and move the where clause to the join so that data for rides only returns when ride_miles > 100 if a ride. – xQbert Mar 19 '14 at 16:25

1 Answers1

0

You could do a LEFT OUTER JOIN so that it will only return the ride information for rides that are over 100 miles, but I think you might want to break up the query so that you have "bikes with a century on them" and "rides for those/all bikes". But to get your original query closer I would do something like

SELECT b.bike_name, r.ride_date, r.ride_miles
    FROM bikes b
    LEFT OUTER JOIN rides r ON r.bike_name = b.bike_name 
        AND DATE_FORMAT(r.ride_date, '%Y') = '$current_year'
        AND ride_miles > 100

If the ride_date and ride_miles are NULL then you know the bike never had a century on it. Any bike that has at least one non-NULL ride_date has done a century.

Danny
  • 1,740
  • 3
  • 22
  • 32
  • Thanks for all of your responses! So it looks like xQbert and Danny's responses are basically the same right? For both the WHERE clause has been moved to the join? The only difference is the right join vs the left join? In testing the two queries it looks like the LEFT JOIN is the one that comes back with the right data. I've read up extensively on joins and am still confused when it comes to them. – sspeed Mar 19 '14 at 16:44
  • Crap, I made this too simple, I realized I need more query parameters that would invalidate the responses. I'll award points and ask a new question. – sspeed Mar 19 '14 at 16:51
  • New question here, sorry... http://stackoverflow.com/questions/22513000/showing-the-longest-distance-for-each-bike-and-a-green-icon-if-over-100 – sspeed Mar 19 '14 at 17:06