How should I use NOT IN statement in MySQL
I am trying to use NOT IN statement with MySQL. However, I get 0 row with code below (no syntax error). I am sure there should be more than 0 row with the statement. What syntax should I adjust?
SELECT DISTINCT member_id
FROM client_payments
INNER JOIN client_purchase_records ON client_purchase_records.id = client_payments.purchase_record_id
WHERE status = 1
AND client_payments.created_at > '2021-10-28 00:00:00'
AND client_payments.created_at < '2021-10-31 23:59:00'
NOT IN(
SELECT DISTINCT member_id
FROM client_payments
INNER JOIN client_purchase_records ON client_purchase_records.id = client_payments.purchase_record_id
WHERE status = 1
AND client_payments.created_at > '2020-9-30 00:00:00'
AND client_payments.created_at < '2021-10-27 23:59:00'
);
Difference about two query is mainly about created_at column, I want to do "set difference operation" with period A(2021-10-28 00:00:00 - 2021-10-31 23:59:00 )and period B(2020-9-30 00:00:00 - 2021-10-27 23:59:00)
I want to query out member_id who pay during 2020-9-30 00:00:00 - 2021-10-27 23:59:00
Subtract with member_id who pay during 2021-10-28 00:00:00 - 2021-10-31 23:59:00
Finally I get member_id who pay during 2021-10-28 00:00:00 - 2021-10-31 but not pay during 2020-9-30 00:00:00 - 2021-10-27 23:59:00 ( new member_id never show before)
Copyright License：
Author:「fung」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/69807384/how-should-i-use-not-in-statement-in-mysql
About “How should I use NOT IN statement in MySQL” questions
I am trying to use NOT IN statement with MySQL. However, I get 0 row with code below (no syntax error). I am sure there should be more than 0 row with the statement. What syntax should I adjust?
SE...
|
I have set MySQL variable:
SET @test = 'my_db';
select @test;
Then
use @test
ERROR 1049 (42000): Unknown database '@test'
OR
mysql> SET @test = 'my_db';
Query
|
MySQL experts. current data in table. COLUMN NAME IS URL
/abc1/pqr
/abc10/pqr
I need below result ie abc1 and abc10 should be replaced to xyz
/xyz/pqr
/xyz/pqr
How can I do this using replace com...
|
How do i take advantage of MySQL's ability to cache prepared statements?
One reason to use prepared statements is that there is no need to send the prepared statement itself multiple times if the s...
|
In the following mysql query I'm trying to select all products that have ALL the features in the parenthesis. I'm using IN that apparently returns products that have feature 1 OR feature 2 etc... W...
|
I've read (on using Statement (C# Reference)) that the using statement should be used to release the resources used by managed types (like File and Font) that use unmanaged resources. So started us...
|
how to use like statement in encrypted column in mysql
i need to get user id from a table where username is like some value(user given input). but my username column is locally encrypted and store...
|
I use follow mysql statement to get some info from mysql via php.
( SELECT *
FROM mytable
WHERE qid NOT IN ({$used['used']})
AND level = 1
ORDER BY RAND()
LIMIT 5)
UNION
( SE...
|
First things first ive tried to fix this myself for hours and spent a good time trying to find an answer too but to no avail.. anyways any help wud be greatly appreciated, its for college! :)
i ha...
|
I am new to this.
I want to select rows with 1) content = 'cc' 2) id = 2 3) type should be something like /^ch[1-8]{1,1}l[1-8]{1,1}$/i
Problem is that i don't know how to write it in mysql stateme...
|
|
|