Combine queries into one query

Question

I have the following two tables (with some sample data)

MAGAZINES:

  ID |  SETID |  DATE
 ==========================
  1 |  1 |  2010-02-25
  2 |  2 |  2010-02-25
  3 |  1 |  2010-02-26
  4 |  2 |  2010-02-26
  5 |  1 |  2010-02-27
  6 |  2 |  2010-02-27
  7 |  1 |  2010-02-28
  8 |  2 |  2010-02-28
  9 |  1 |  2010-03-01

STATISTICS:

  ID |  OBJECTID |  FREQUENCY |  STARTID |  Endid
 ===============================================
  1 |  1 |  0.5 |  1 |  5
  2 |  2 |  0.6 |  1 |  5
  3 |  3 |  0.02 |  1 |  5
  4 |  4 |  0.6 |  2 |  6
  5 |  5 |  0.6 |  2 |  6
  6 |  6 |  0.4 |  2 |  6
  7 |  1 |  0.35 |  3 |  7
  8 |  2 |  0.6 |  3 |  7
  9 |  3 |  0.03 |  3 |  7
  10 |  4 |  0.6 |  4 |  8
  11 |  5 |  0.6 |  4 |  8
  7 |  1 |  0.45 |  5 |  nine
  8 |  2 |  0.6 |  5 |  nine
  9 |  3 |  0.02 |  5 |  nine

Every day, new logs are analyzed on different sets of objects and stored in the LOGS table. Among other processes, some statistics are computed for the objects contained in these sets, and the result is stored in the STATS table. These statistics are calculated through several logs (identified by the STARTID and ENDID columns).

So, what could be an SQL query that will give me the latest calculated statistics for all objects with corresponding log dates.
In this example, the result lines are:

  OBJECTID |  SETID |  FREQUENCY |  STARTDATE |  Endate
 =================================================== ====
    1 |  1 |  0.45 |  2010-02-27 |  2010-03-01
    2 |  1 |  0.6 |  2010-02-27 |  2010-03-01
    3 |  1 |  0.02 |  2010-02-27 |  2010-03-01
    4 |  2 |  0.6 |  2010-02-26 |  2010-02-28
    5 |  2 |  0.6 |  2010-02-26 |  2010-02-28

So, the most recent statistics for set 1 are calculated using logs from March 27 to March 1, while statistics for set 2 are calculated from February 26 to 28. Object 6 is not in the result lines, since there are no statistics for the last period.

The last thing I use MySQL.

Any idea?

Answer 1

Does this question fit your question?

SELECT objectid, l1.setid, frequency, l1.date as startdate, l2.date as enddate FROM `logs` l1 INNER JOIN `stats` s ON (s.startid=l1.id) INNER JOIN `logs` l2 ON (l2.id=s.endid) INNER JOIN ( SELECT setid, MAX(date) as date FROM `logs` l INNER JOIN `stats` s ON (s.startid=l.id) GROUP BY setid ) d ON (d.setid=l1.setid and d.date=l1.date) ORDER BY objectid 

Answer 2

If there are no links, you can use a filter connection. For example:

 select stats.objectid , stats.frequency , startlog.setid , startlog.date , endlog.date from stats join logs startlog on startlog.id = stats.startid join logs endlog on endlog.id = stats.endid join ( select objectid, max(endlog.date) as maxenddate from stats join logs endlog on endlog.id = stats.endid group by objectid ) filter on stats.objectid = filter.objectid and filter.maxenddate = endlog.date order by stats.objectid 

The results of your example look a bit inactive, for example, there is no line for objectid 5, where the frequency is 0.35.