Try
SET @judgecode = 'LLL'
SELECT pay.* from Payments pay
JOIN motorvehicle mv ON mv.keyident = pay.pkeyident
AND pay.py_module = 'MV' AND mv_judge = @judgecode
JOIN locallaw ll ON ll.keyident = pay.pkeyident
AND pay.py_module = 'LL' AND ll_judge = @judgecode
...
JOIN smallclaims sc ON sc.keyident = pay.pkeyident
AND pay.py_module = 'SC' AND sc_judge = @judgecode
WHERE pay.py_paydate BETWEEN ...
>Ok this is what I need to do
>
>I have a payments table where I want to select a payment date range and select for a specific judgecode. The judge code could come from up to 5 different tables here are the table sturctures and some values
>
>payments
>py_paydate py_module pkeyident
>'01/01/2005' 'MV' '1'
>'02/01/2005' 'SC' '2'
>'04/01/2005' 'CRM' '3'
>'04/01/2005' 'LL' '4'
>'01/01/2005' 'CV' '5'
>
>MV
>keyident mv_judge
>'1' 'MMM'
>
>SC
>keyident sc_judge
>'2' 'LLL'
>
>LL
>keyident ll_judge
>'4' 'LLL'
>
>CRM
>keyident crm_judge
>'3' 'AAA'
>
>CV
>keyident sc_judge
>'5' 'LLL'
>
>where py_module determines which module the payment came from
>
>so in my select statement I want to select all payments
>from 01/01/2005 - 12/31/2005 with judge code ='LLL'
>
>Hope this helps explain what I am trying to do a little better !
--sb--