title: Trying to join 2 tables based on the most recent timestamp *before* a specific date
body: <p>Here is my SQL statement, which seemed to function perfectly well before we created a new database. This approach seems to work just fine on another, similarly structured, pair of tables.</p>
  <pre><code>SELECT *
  FROM tele2_details AS d
      INNER JOIN 
      tele2_usage AS u
      ON    
          d.iccid = u.iccid
      AND
          u.timestamp = (
          SELECT MAX(u.timestamp)
          FROM tele2_usage 
          WHERE u.timestamp &lt;= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
        )
  WHERE
      accountCustom1='Horizon'
  </code></pre>
  <p>What I'm attempting to do here is join the details table with the usage table, the usage rows just contains the iccid of a sim card, a timestamp, and their current usage in bytes. What this should do is find the most recent usage record <em>before</em> the specified date (2022-01-08 09:30:00). This should give me a set of sims, each joined with it's most recent usage record before the specified time, however I usually get zero results on this particular combo of tables.</p>
  <p>Specifically though, it <em>does</em> match any records where the date is the exact same as specified in the query, but not dates that are before or equal to the specified date. Can anybody help me with where I'm going wrong. This query worked fine in a previous database, we're rebuilding our systems and this has now appeared as an issue.</p>
  <p>Thanks in advance for any help.</p>
  <hr />
  <p><em>Edit</em>
  Here's some more information, that I hope will make the question make more sense. So here is an outline of the details table, I've removed some of the columns but this is at least illustrative of table.</p>
  <pre><code>CREATE TABLE `tele2_details` (
      `iccid` VARCHAR(255) NOT NULL,
      `msisdn` VARCHAR(255) NOT NULL,
      `status` VARCHAR(255) NOT NULL,
      `ratePlan` VARCHAR(255) NOT NULL,
      `communicationPlan` VARCHAR(255) NOT NULL,
      PRIMARY KEY (`iccid`)
  )
  COLLATE='utf8mb4_0900_ai_ci'
  ENGINE=InnoDB
  ;
  </code></pre>
  <p>Then we also have a usages table, which stores sample of the data usage of sim cards, along with timestamps...</p>
  <pre><code>CREATE TABLE `tele2_usage` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `iccid` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
      `msisdn` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
      `timestamp` DATETIME NOT NULL,
      `ctd_data_usage` BIGINT NOT NULL,
      `ctd_sms_usage` BIGINT NOT NULL,
      `ctd_voice_usage` BIGINT NOT NULL,
      `session_count` INT NOT NULL,
      PRIMARY KEY (`id`)
  )
  COLLATE='utf8mb4_hr_0900_ai_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=10116319
  ;
  </code></pre>
  <p>The query I'm trying to create should return a set of sim details, joined with a usage record which is <strong>closest to, but not after, a particular time.</strong></p>
  <p>So if you look at the original query at the top, I'm trying to join the details onto the usage record which is **closest to, but not after 2022-01-08 09:30:00 **</p>
  <p>I hope that makes sense.</p>
  <p>Let's have a look at a particular sim</p>
  <pre><code>SELECT iccid, msisdn, status, ratePlan, communicationPlan FROM tele2_details WHERE iccid='xxxx203605100034xxxx'
  </code></pre>
  <p>results in 1 match</p>
  <pre><code>&quot;xxxx203605100034xxxx&quot; &quot;xxxx9120012xxxx&quot; &quot;ACTIVATED&quot; &quot;Pay as use - Existing Business&quot; &quot;Data LTE&quot;
  </code></pre>
  <p>And if I look in the usage table for that same sim I can see many records that should satisfy my conditions</p>
  <pre><code>SELECT id, iccid, TIMESTAMP, ctd_data_usage FROM tele2_usage WHERE iccid='xxxx203605100034xxxx' AND TIMESTAMP &lt;= '2022-01-08 09:30:00'
  </code></pre>
  <p>results in</p>
  <pre><code>&quot;10096279&quot;  &quot;xxxx203605100034xxxx&quot;  &quot;2022-01-08 09:01:00&quot;   &quot;77517560&quot;
  &quot;10092271&quot;  &quot;xxxx203605100034xxxx&quot;  &quot;2022-01-08 08:01:03&quot;   &quot;77002733&quot;
  &quot;10088263&quot;  &quot;xxxx203605100034xxxx&quot;  &quot;2022-01-08 07:01:11&quot;   &quot;76270445&quot;
  &quot;10084255&quot;  &quot;xxxx203605100034xxxx&quot;  &quot;2022-01-08 06:01:05&quot;   &quot;76270445&quot;
  </code></pre>
  <p>of which I would like to select the first record (with the 09:01 timestamp) for joining on to the details record. I can get that timestamp with the following query</p>
  <pre><code>SELECT MAX(timestamp)
  FROM tele2_usage 
  WHERE TIMESTAMP &lt;= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') AND iccid='xxxx203605100034xxxx'
  </code></pre>
  <p>which results in '2022-01-08 09:01:00', which is exactly what I want. So now I put it all together...</p>
  <pre><code>SELECT *
  FROM tele2_details AS d
      INNER JOIN 
      tele2_usage AS u
      ON    
        d.iccid = u.iccid
      AND
        u.timestamp = (
          SELECT MAX(timestamp)
          FROM tele2_usage 
          WHERE timestamp &lt;= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
        )
  WHERE
      accountCustom1='Horizon' AND iccid='xxxx203605100034xxxx'
  </code></pre>
  <p>And i get nothing! I would expect to get back the details record joined with that particular sim, but actually I get zero results and I don't understand why.</p>
  <p>Ideally I would remove the final AND for the iccid and I would expect to recieve a set of all the sims for that client with the usage from closest to but not after the specified date.</p>
  <p>So with that explanation, does anyone know why I'm not getting any records? I have a similar table for another sim provider that structured exactly the same, with a details table and a usage table and this query works just fine on that table. I simply can't understand why this doesn't work.</p>
  <hr />
  <p><em>Edit 2</em>
  @Serg suggested trying to alias the subquery (I think that's what it's called) which resulted in the following code...</p>
  <pre><code>SELECT *
  FROM tele2_details AS d
      INNER JOIN 
      tele2_usage AS u
      ON    
        d.iccid = u.iccid
      AND
        u.timestamp = (
          SELECT MAX(u2.timestamp)
          FROM tele2_usage AS u2
          WHERE u2.timestamp &lt;= DATE_FORMAT('2022-01-08 09:30:00','%Y-%m-%d %H:%i:%s') 
        )
  WHERE
      accountCustom1='Horizon'
  </code></pre>
  <p>Unfortunately this still resulted in zero results.</p>