SQL: How to select the record with the most recent past date for every record on other table.

Question

How can I reformulate this two queries in one single query on MySQL?

SELECT * FROM tvnetwork  
//this query gives me a list of TV networks

But networks usually change names and logos, so for each of the retrieved TV networks:

SELECT name, logo FROM tvnetworkinfo 
  WHERE id = $tvnetwork.id 
  AND date_since < NOW()
    ORDER BY date_since desc LIMIT 1

//this one gives me the most recent logo and name for the network

I intentionally leave out the NEXT name/logo change. E.g. I want "NatGeo" instead of the old "National Geographic", but I also want "SciFi" instead of the not yet implemented "SyFy".

I'd like to retrieve everything in a single query. ¿Is there any way to do that?

Answer 1

To get the most recent list of network names & logos, use:

SELECT x.name,
       x.logo
  FROM (SELECT tni.name,
               tni.logo
               CASE 
                 WHEN @name = tni.name THEN @rownum := @rownum + 1 
                 ELSE @rownum := 1
               END AS rank
               @name := tni.name
          FROM TVNETWORKINFO tni
       -- JOIN TVNETWORK tn ON tn.id = tni.id
          JOIN (SELECT @rownum := 0, @name := '') r
         WHERE tni.date_since < NOW()
      ORDER BY tni.name, tni.date_since DESC) x
 WHERE x.rank = 1

I disregarded the JOIN to the TVNETWORK table, it didn't seem to be necessary to the output. Uncomment it by removing the double hyphen before it in the query I provided.