Sampling from two microsoft sql 2008 tables?

Question

SQL

There are two tables:
USERS (id, email) a table of users with email
USER_SERVICE_PAYMENT (id, user_service_id) a table indicating the date of access to a particular section on the site
You need to select users from the USER_SERVICE_PAYMENT table with access to section 1 with an access period from 01.05 .2014 until 12/31/2100 at the same time see the e-mails of the user data.

select * from USER_SERVICE_PAYMENT 
where USER_SERVICE_ID = '1' and paid_until between '2014-05-01' and '2100-12-31'

I get a list, but I can't deal with INNER JOIN in any way.

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

does not work.

Answer 1

If USER_SERVICE_ID is a userid (same as id in USERS), then something like this:

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

Why is there "EMAIL = 'NULL'" - I did not understand. With it, it will not select users who have a box specified.

Answer 2

I'm not a specialist in MSCL, but I think you're joining id with email, so you can't. you need to join by the column by which these tables define the user, like user_id in one table = 5, in the second table the same ID is taken and joined to the resulting selection

Answer 3

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

this is how the request goes through, but all the values ​​​​are empty.

Answer 4

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

You have a JOIN by user ID and section ID, this is not correct
Somehow it should be in theory

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'