M
M
modnews2014-05-03 14:12:46
SQL
modnews, 2014-05-03 14:12:46

Sampling from two microsoft sql 2008 tables?

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 the question

In order to leave comments, you need to log in

4 answer(s)
V
Vladlen Grachev, 2014-05-03
@modnews

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.

I
Ivan Somov, 2014-05-03
@jsom

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

M
modnews, 2014-05-03
@modnews

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.

L
lizanin, 2014-05-04
@lizanin

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'

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question