I need help with this query. I have records in customer table
Main table - M
+---------+---------+----------+
| company | Account | Active |
+---------+---------+----------+
| A | A123 | Active |
| A | A123 | InActive |
| B | B123 | Inactive |
+---------+---------+----------+
Reference table - R
+---------+---------+
| company | Account |
+---------+---------+
| A | A123 |
| B | B123 |
| C | C123 |
+---------+---------+
Pull all records from M which exists in R but are inactive. The problem here is a company may have both an inactive and an active account in M, I want to pull only those that only have inactive account and no active account. So from the two table, I should get
+---------+---------+----------+
| company | Account | Active |
+---------+---------+----------+
| B | B123 | Inactive |
+---------+---------+----------+
Because A while it does exist in R, it also has an Active Account status so I must exclude it.
Best Answer
SO I assume it can be only one active and one inactive row. Here is a solution with JOINs only. If there can be multiple (active) rows, you should use
EXISTS
instead, as @ypercube already mentioned in his comment.As @ypercube commented: it did not become entirely clear whether you don't want any active account to invalidate the company or just a matching active account.
I went with
matching
account in my query. RemoveAND M2.account = R.account
from the JOIN condition if you wantany
.