Geek asks and answers

SQL: how to select a record with the last last date for each record in another table

How can I reformulate these two queries in one query in 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 received television 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 the name / logo unchanged. For instance. I want "NatGeo" instead of the old "National Geographic", but I also want "SciFi" instead of the not yet implemented "SyFy".

I would like to get everything in one request. ¿Is there a way to do this?

0
source share
1 answer

To get the most recent list of network names and 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

JOIN TVNETWORK, . , , .

+4

More articles:


All Articles