SELECT *
  FROM transfers a
 WHERE a.location_code_from = 'ABC'
   AND a.season = 2011
   AND EXISTS (SELECT 'Y'
          FROM available_transactions b
         WHERE b.available_qty > 0
           AND a.season = b.season
           AND a.transfer_release_no = b.release_no);

SELECT *
  FROM transfers a, available_transactions b
 WHERE a.location_code_from = 'ABC'
   AND a.season = 2011
   AND b.available_qty > 0
   AND a.fin_year = b.fin_year
   AND a.transfer_release_no = b.release_no;

SELECT *
   FROM transfers a
  WHERE a.location_code_from = 'ABC'
    AND a.season = 2011
    AND (a.season, a.transfer_release_no) IN
        (SELECT b.season, a.transfer_release_no
           FROM available_transactions b
          WHERE b.available_qty > 0)

where id in (select a.id
             from table_a a, table_b b
             where a.id = b.id
               and a.label = 'X'
            )