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.............