>Thanks that worked !! one additional request is there a way to include the judgecode as one of the columns in the result set ?

You can add to the results all the homogeneous fields of motorvehicle, locallaw...,
add they to the UNION's "select":

SELECT @judgecode = 'LLL'
SELECT pay.*,jj.judge  from Payments pay
  JOIN
  (SELECT 'MV' module,keyident,mv_judge judge FROM motorvehicle 
    UNION ALL
   SELECT 'LL' module,keyident,ll_judge       FROM locallaw 
    UNION ALL
   ....
    UNION ALL
   SELECT 'SC' module,keyident,sc_judge       FROM smallclaims ) jj
   ON     jj.keyident = pay.pkeyident 
      and jj.module   = pay.py_module 
      and jj.judge    = @judgecode
  WHERE pay.py_paydate BETW.............