lizanin

INNER JOIN USERS as B ON (A.USER_SERVICE_ID = B.USER_ID)

У вас JOIN по ID юзера и ID раздела, это не верно

Как-то так должно быть по идее

SELECT *
    FROM USER_SERVICE_PAYMENT as A
        INNER JOIN USERS as B ON (A.ID = B.USER_ID)
    WHERE A.USER_SERVICE_ID = '1' and A.paid_until between '2014-05-01' and '2100-12-31'