MySQL get rows where corresponding row in same table exists

Question

I'm trying to get rows from a table according to some basic where clauses, and now I want to include an "AND EXISTS" clause on the end. My code is the following:

$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 = ? )
        ");

The problem I'm having is that I don't know how to specify a column value from the main query within the AND EXISTS subquery.

I'm looking for a way to tack on this bit into the subquery:

AND object_id = **object_id from main query**

Any help appreciated

AgRizzo · Accepted Answer · 2013-10-08 09:51:25Z

1

Also, added an alias to the subquery's table to avoid confusion

$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 = ? )
    ");

answered Oct 8, 2013 at 9:51

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Allerion Over a year ago

This worked for me, I didn't realise you could alias in MySQL :) Many thanks

paul · Accepted Answer · 2013-10-08 09:54:02Z

1

Rather than EXISTS, you might find a self-join syntax is clearer here, I cannot deduce exactly what you want from your code, but to get object_id for an event that started before a specific time, and was also started again later:

SELECT  ue1.object_id 
FROM    user_events ue1 join user_events ue2
WHERE   ue1.event_id = ue2.event_id AND
        ue1.object_id = ue2.object_id AND
        ue1.event_id = ? AND 
        LPAD(ue1.start_timestamp, 15, '0') < LPAD(ue2.start_timestamp, 15, '0') AND 
        LPAD(ue1.start_timestamp, 15, '0') < LPAD(?, 15, '0')

answered Oct 8, 2013 at 9:54

paul

22.1k1 gold badge56 silver badges54 bronze badges

1 Comment

Allerion Over a year ago

Sorry I should have clarified further, the second event is actually a different one (with different id) but belonging to same object. I've read that EXISTS can be time-consuming, so I will test your code as well. Many thanks

Shadow Radiance · Accepted Answer · 2013-10-08 09:45:20Z

0

and object_id in (select object_id from ...

answered Oct 8, 2013 at 9:45

Shadow Radiance

1,35913 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL get rows where corresponding row in same table exists

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related