When you are creating an advanced alert, and you are on the "Trigger Condition" tab. The top dropdown for "Type of Property to Monitor" has an additional option "Custom SQL Alert". This then shows you another drop down with the type of object you want to write your SQL against and shows you the top part of your SQL statement.
The bottom area is where you type in your WHERE clause or whatever other INNER JOIN SQL you come up with. Any valid SQL should work in this area.
As a matter of good practice, I would type the SQL in Management Studio first and then paste it in here once you have what you want.
Thanks Karlo, I missed that somehow.