id  name            screen_name     deleted_at
9   Series Name 9   Screen Name 9   NULL
11  Series Oneb     seriesoneb      NULL
15  Mad Dogs        maddogs         NULL

id  series_id   subject     deleted_at
8   15          Subject 8   NULL
13  11          Subject 13  NULL
28  9           Subject 28  NULL
34  11          Subject 34  NULL
37  9           Subject 37  NULL
41  9           Subject 41  NULL
48  9           Subject 48  NULL

SELECT *
FROM `tickets`
WHERE (
    SELECT count( * )
    FROM `series`
    WHERE `series`.`deleted_at` IS NULL
    AND `tickets`.`series_id` = `series`.`id`
    AND screen_name LIKE '%series name%'
    OR name LIKE '%series name%'
    AND `series`.`deleted_at` IS NULL
)

SELECT *
FROM `tickets`
WHERE (
    SELECT count( * )
    FROM `series`
    WHERE `series`.`deleted_at` IS NULL
    AND `tickets`.`series_id` = `series`.`id`
    AND (screen_name LIKE '%series name%' OR name LIKE '%series name%')
    AND `series`.`deleted_at` IS NULL
)

WHERE (`series`.`deleted_at` IS NULL AND
        `tickets`.`series_id` = `series`.`id` AND
        screen_name LIKE '%series name%'
       ) OR
       (name LIKE '%series name%' AND
        `series`.`deleted_at` IS NULL
       )

WHERE `series`.`deleted_at` IS NULL AND
        `tickets`.`series_id` = `series`.`id` AND
        (screen_name LIKE '%series name%' OR
         name LIKE '%series name%'
        )