Here are my two tables:
product table
| id | product name |
|---|---|
| 1 | Product A |
| 2 | product B |
authorized table
| id | product id | Authorizer |
|---|---|---|
| 1 | 1 | Officer |
| 1 | 1 | Manager |
| 2 | 2 | Officer |
I want to select all products from product table which only authorized by officer. But not these product which authorized both officer and manager. Because if it authorized by officer and manager that means these product are valid or authorized. just select these product which is only authorized by officer from authorized table. Because these product still unauthorized
CodePudding user response：
SELECT *
FROM product
JOIN authorized
ON product.id = authorized.product_id
WHERE authorized.Authorized = "Officer"
AND product.id NOT IN (SELECT product_id FROM authorized WHERE Authorizer = "Manager")
I joined both tables (using the foreign key product_id, I assume) and checked that the id isn't found in the "Authorized" table if the Authorizer is "Manager"
EDIT: If there's others "Authorizer" that should be included, replace Authorizer = "Manager"
by Authorizer != "Officer"