Here is a simple solution with a subquery. In the subquery we find records where Flag_Val is equal to "N".

SELECT Rec_ID, Flag_Val FROM table1 WHERE Flag_Val = 'Y' AND
Rec_ID IN (SELECT Rec_ID FROM table1 WHERE Flag_Val = 'N');