I have three tables
users table
+--------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+----------------+ | Id | bigint | NO | PRI | NULL | auto_increment | | AccountNumber | varchar(50) | YES | | NULL | | | DisplayName | varchar(255) | YES | MUL | NULL | | | ParentAccountId | bigint | YES | MUL | NULL | | | AccountTypeRef | bigint | YES | MUL | NULL | | | PhoneNo | varchar(255) | YES | | NULL | | | FaxNo | varchar(50) | YES | | NULL | | | Website | varchar(200) | YES | | NULL | | | Email | varchar(100) | YES | | NULL | | | StateRef | bigint | YES | MUL | NULL | | | Address | longtext | YES | | NULL | | | PostalCode | varchar(50) | YES | | NULL |
products table
+-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | Id | bigint | NO | PRI | NULL | auto_increment | | ProductId | bigint | YES | MUL | NULL | | | PackName | varchar(255) | YES | | NULL | | | | Price | double | YES | | NULL | | | OnlineInfoAddress | varchar(255) | YES | | NULL | | | Description | longtext | YES | | NULL | | | IsDelete | tinyint | YES | | NULL | | | CreateUserId | bigint | YES | | NULL | | | CreateDate | datetime | YES | | NULL | | | EditDate | datetime | YES | | NULL | | | EditUserId | bigint | YES | | NULL | | | Tag | int | YES | | NULL | | | Ref | varchar(50) | YES | | NULL | | | deleted_at | timestamp | YES | | NULL | | +-------------------+--------------+------+-----+---------+----------------+
users_buy table
+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | Id | bigint | NO | PRI | NULL | auto_increment | | users_id | bigint | YES | MUL | NULL | | | product_id | bigint | YES | MUL | NULL | | | BuyDate | datetime | YES | | NULL | | | CountBuy | int | YES | | NULL | | | TotalPrice | double | YES | | NULL | | | Description | longtext | YES | | NULL | | | invoiceNumber | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+
I want to get users who did not buy a specific product
for example, user number 20 bought 5 product with ids [1,2,3,4,5] and user number 19 bought 3 product with ids [1,2,3]
And user number 18 bought 2 product with ids [1,3]
I want to get users who did not buy any product with the id 5
ie I want user 18 and 19
How can I do this?
Advertisement
Answer
This will list of all users that did not buy product id 5:
SELECT *
FROM users
WHERE
Id NOT IN (
SELECT users_id
FROM users_buy
WHERE product_id = 5
)