php - MySQL 获取同一个表中存在对应行的行

Question

我试图根据一些基本的 where 子句从表中获取行，现在我想在末尾包含一个“AND EXISTS”子句。我的代码如下:

$stmt = $mysqli->prepare("SELECT object_id FROM ".
          $db_table_prefix."user_events
          WHERE LPAD(start_timestamp,15,'0') < LPAD(?,15,'0')
          AND event_id = ?
          AND EXISTS ( 
            SELECT * FROM ".$db_table_prefix."user_events
              WHERE LPAD(start_timestamp,15,'0') > LPAD(?,15,'0')
              AND event_id = ? )
        ");

我遇到的问题是我不知道如何在 AND EXISTS 子查询中指定主查询的列值。

我正在寻找一种方法来将这一点添加到子查询中:

AND object_id = **object_id from main query**

感谢任何帮助

Answer 1

此外，还向子查询的表添加了别名以避免混淆

$stmt = $mysqli->prepare("SELECT object_id FROM ".
      $db_table_prefix."user_events
      WHERE LPAD(start_timestamp,15,'0') < LPAD(?,15,'0')
      AND event_id = ?
      AND EXISTS ( 
        SELECT * FROM ".$db_table_prefix."user_events AS u
          WHERE LPAD(u.start_timestamp,15,'0') > LPAD(?,15,'0')
          AND u.object_id = " .$db_table_prefix.".object_id
          AND u.event_id = ? )
    ");