MySQL query select

Question

I have two tables:

t1
____________
projectID
userID
projectExpiration

t2
____________
usrID
subscriptioID
subscriptionExpiration

I need to select porject ID from T1 where the following conditions are met:

t1.projectExpiration = older then 3 months
t2.subscriptioID = '5'
t2.expiration = older then 3 months

The user may have other subscriptions in t2. I need ONLY the results where they have a single subscription entry which has an ID of '5'

I need help putting it all together.

Here's what I've got so far:

SELECT projectID
FROM t1
LEFT JOIN t2 ON (t1.userID = t2.userID)
WHERE t1.projectExpiration < (CURDATE() - INTERVAL 3 MONTH)
  AND t2.subscriptionExpiration = 5
      AND t2.subscriptionExpiration < (CURDATE() - INTERVAL 3 MONTH)

Feel like I'm stuck...

You refer to "ONLY" if they have a single subscription of ID = 5... Does this mean if the user has 1, 2, 3 and 5 you want to skip this person??? or did you mean you only want those who HAVE and ID 5 regardless of any other subscription they may also have... two different queries... Also, what if they subscribed to 5, but one was already expired, and they have another more CURRENT subscription to #5 -- do you want that??? — DRapp
– DRapp, Commented Jan 10, 2012 at 18:57
Grammar police here: the correct usage is 'than'. As in "If it is older than 3 months, then it is expired" — dar7yl
– dar7yl, Commented Jan 10, 2012 at 20:14

Eric · Accepted Answer · 2012-01-10 19:00:32Z

1

You want a not exists check to make sure 5 is the only row:

select
    *
from
    t1 x
    inner join t2 y on
        x.userid = y.userid
where
    x.projectexpiration < curdate() - interval 3 month
    and y.subscriptionid = 5
    and not exists (
        select
            1
        from
            t2 z
        where
            z.userid = x.userid
            and z.subscriptionid <> 5
    )

I should also mention that I used an inner join in lieu of a left join here. This is because you don't want to grab rows where a t2 row doesn't exist (you're otherwise not accounting for nulls in your where clause). So, it's just needless overhead. An inner join gets us down to the result set you want in a much clearer fashion.

edited Jan 10, 2012 at 19:00

answered Jan 10, 2012 at 18:53

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

santa Over a year ago

Wow, great! Did you mean y.subscriptioID instead of y.subscriptionexpiration ?

Eric Over a year ago

@santa - Yup. Was copying that part blindly from your post :)

RolandoMySQLDBA · Accepted Answer · 2012-01-10 19:36:01Z

1

SELECT
    sq1.projectID
FROM
    (SELECT * FROM T2 WHERE subscriptioID=5
    AND subscriptionExpiration < DATE(now() - INTERVAL 3 MONTH)) sq2
    INNER JOIN
    (SELECT * FROM T1 WHERE projectExpiration < DATE(now() - INTERVAL 3 MONTH)) sq1
    ON sq12.usrID=sq1=userID
;

or

SELECT
    sq1.projectID
FROM
    (SELECT usrID userID,subscriptioID,subscriptionExpiration
    FROM T2 WHERE subscriptioID=5
    AND subscriptionExpiration < DATE(now() - INTERVAL 3 MONTH)) sq2
    INNER JOIN
    (SELECT * FROM T1 WHERE projectExpiration < DATE(now() - INTERVAL 3 MONTH)) sq1
    USING (userID)
;

or

SELECT
    sq1.projectID
FROM
    (SELECT * FROM T2 WHERE subscriptioID=5
    AND subscriptionExpiration < DATE(now() - INTERVAL 3 MONTH)) sq2
    INNER JOIN
    (SELECT projectID,userID usrID,projectExpiration         
    FROM T1 WHERE projectExpiration < DATE(now() - INTERVAL 3 MONTH)) sq1
    USING (usrID)
;

edited Jan 10, 2012 at 19:36

answered Jan 10, 2012 at 19:19

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

1 Comment

santa Over a year ago

I just noticed I put userID in t2. It is actually usrID, while userID in t1. Sorry, my mistake. I assume you code will change a bit too.

Collectives™ on Stack Overflow

MySQL query select

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related