SQL query advice (replacing exists with joins)

Question

I have table with following fields and indexes:
I need to make a query that will do:

check table, if there are Dial events, after which (with same unique_id) no Bridge or Hangup events comes

or

if there are NewCallerId events, with no Hangup events after it (with same unique_id) or no Dial events (with call_id = current unique_id)

There was one query, but I didn't make it work, and users answers didn't help either. Here's the query that does the work in 395 seconds (too long):

SELECT t1.* 
FROM   ASTERISK t1 
WHERE  EXISTS (SELECT t2.* 
               FROM   ASTERISK t2 
               WHERE  t1.OPERATOR_DIAL = '$extension' 
                      AND t1.EVENT = 'Dial' 
                      AND NOT EXISTS (SELECT t3.* 
                                      FROM   ASTERISK t3 
                                      WHERE  t3.UNIQUE_ID = t1.UNIQUE_ID 
                                             AND ( t3.EVENT = 'Hangup' 
                                                    OR t3.EVENT = 'Bridge' ))) 
        OR EXISTS (SELECT t4.* 
                   FROM   ASTERISK t4 
                   WHERE  t1.EVENT = 'NewCallerid' 
                          AND t1.OPERATOR_DIAL = '$extension' 
                          AND NOT EXISTS (SELECT t5.* 
                                          FROM   ASTERISK t5 
                                          WHERE  ( t5.UNIQUE_ID = t1.UNIQUE_ID 
                                                   AND t5.EVENT = 'Hangup' ) 
                                                  OR ( t5.CALL_ID = t1.UNIQUE_ID 
                                                       AND t5.EVENT = 'Dial' ))) 
ORDER  BY DATE DESC 
LIMIT  1 

Tried to rewrite it with JOINS, but getting #1248 - Every derived table must have its own alias

This is my attempt with a JOIN query:

SELECT t1.*
                    FROM asterisk t1
                    INNER JOIN (SELECT t2.* FROM asterisk t2
                                  WHERE
                                    t1.operator_dial = '$extension'
                                  AND t1.event = 'Dial'
                                  LEFT OUTER JOIN (SELECT t3.* FROM asterisk t3
                                                  WHERE t3.unique_id = t1.unique_id AND (t3.event = 'Hangup' OR t3.event = 'Bridge')))
                          OUTER JOIN (SELECT t4.* FROM asterisk t4
                                    WHERE t1.event = 'NewCallerid'
                                    AND t1.operator_dial = '$extension'
                                    LEFT OUTER JOIN (SELECT t5.* FROM asterisk t5
                                                    WHERE (t5.unique_id = t1.unique_id AND t5.event = 'Hangup') OR (t5.call_id = t1.unique_id AND t5.event = 'Dial')))
                    ORDER BY DATE DESC
                    LIMIT 1

To show, how the query should work I made 2 screenshots (top part has some calls that will not be in query result and bottom part is one call that must be in query result:

Answer 1

I have just start posting here on stack exchange. Without diving to deeply I noticed that the inner Joins for t2 and t4 do not have values that match up to any values in t1 (could be why the original query was so long).

old query removed.......

Thanks for the screenshot, that helps. So what you want your query to return are active calls (calls that do not have 'hang-ups' or 'Bridges')Then you want NewcallerID events that do not have an associated dial event and Finally dial events with a bridge before them but no bridge after them

SELECT * 
FROM Asterisk t1
WHERE t1.unique_id not in 
    (Select unique_id 
       from Asterisk t2
        Where event = 'Hangup', 'Bridge')
UNION
select *
from asterisk t3
Where event = 'NewCallerID'
    and t3.unique_id not in
      (Select unique_id 
       from Asterisk t4
        Where event = 'Dial')
UNION
Select distinct *
from asterisk t5
    inner join asterisk t6
        on t5.unique_ID = t6.Unique_id
            and t6.event = 'Dial'
Where t5.event = 'Bridge'
         and t6.id > t5.id

I think I have covered all the cases here. (Smells a bit hackish though). Edit: event where a bridge exists before AND After Dial not covered