CODEWITHSUNDEEP
mysql
Akaash
Akaash

Reputation: 171

How to correct and rewrite this query?

SELECT * 
FROM users
WHERE id
IN ( 2024 ) 
AND id NOT IN (
    SELECT user_id
    FROM  `used` 
    WHERE DATE_SUB( DATE_ADD( CURDATE( ) , INTERVAL 7 DAY ) , INTERVAL 14 DAY ) <= created)
AND id NOT IN (
    SELECT user_id
    FROM coupon_used
    WHERE code =  'XXXXX')
AND id IN (
    SELECT user_id
    FROM accounts)

I have id 2024 in users table, but this id 2024 is there in used tables. So when I run this query, it shows me 2024 id also, which should be filtered out. I run the query where I selected specific users, and then I want these user to be filter out that they should not be in used table. But above query is not giving me the desire result. Desire Result is that I want to Select Users by following conditions: Take Specific Users, and check that they are not in used table and not in coupon_used table but they should be in accounts table.

Upvotes: 0

Views: 78

Answers (3)

Kickstart
Kickstart

Reputation: 21513

Firstly, try something like this using joins. Which should be easier to read and (depending on the version of MySQL) faster

SELECT DISTINCT users.* 
FROM users
INNER JOIN accounts ON users.id = accounts.user_id
LEFT OUTER JOIN coupon_used ON users.id = coupon_used.user_id AND coupon_used.code = 'XXXXX'
LEFT OUTER JOIN `used` ON users.id = `used`.user_id AND DATE_SUB( DATE_ADD( CURDATE( ) , INTERVAL 7 DAY ) , INTERVAL 14 DAY ) <= `used`.created
WHERE id IN ( 2024 ) 
AND coupon_used.user_id IS NULL
AND `used`.user_id IS NULL

EDIT - Simplifying the date check:-

SELECT DISTINCT users.* 
FROM users
INNER JOIN accounts ON users.id = accounts.user_id
LEFT OUTER JOIN coupon_used ON users.id = coupon_used.user_id AND coupon_used.code = 'XXXXX'
LEFT OUTER JOIN `used` ON users.id = `used`.user_id AND DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) <= `used`.created
WHERE id IN ( 2024 ) 
AND coupon_used.user_id IS NULL
AND `used`.user_id IS NULL

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

I would recommend using a JOIN on accounts and LEFT OUTER JOINs on the other two tables. A JOIN on accounts means it must be in the accounts table. LEFT OUTER JOINS on the coupon_used and used means it will return a record no matter if they're in that table or not. Filtering down to c.user_id IS NULL means that there is NOT a record in that table.

SELECT users.* 
FROM users
    JOIN accounts ON users.id = accounts.user_id
    LEFT OUTER JOIN coupon_used c ON users.id = c.user_id AND c.code = 'XXXXX'
    LEFT OUTER JOIN `used` u ON users.id = u.user_id AND DATE_SUB( DATE_ADD( CURDATE( ) , INTERVAL 7 DAY ) , INTERVAL 14 DAY ) <= u.created
WHERE id IN ( 2024 ) 
AND c.user_id IS NULL
AND u.user_id IS NULL

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173522

I would use left joins for the exclusion conditions and a regular join for the inclusions:

SELECT users.*
FROM users
INNER JOIN accounts ON accounts.user_id = users.id
LEFT JOIN used ON used.user_id = users.id AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= used.created)
LEFT JOIN coupon_used ON coupon_used.user_id = users.id AND coupon_used.code = 'XXXX'
WHERE id IN (2024) AND used.user_id IS NULL AND coupon_used.user_id IS NULL

I've edited the date manipulation as well; +7 -14 would be -7 :)

Upvotes: 1

Related Questions