pull records that are inactive

Question

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.

Answer 1

a company may have both an inactive and an active account in M

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.

SELECT R.*, M1.active
FROM   R
JOIN   M AS M1 ON M1.company = R.company
              AND M1.account = R.account
              AND M1.active  = 'inactive'
LEFT   JOIN M AS M2 ON M2.company = R.company
                   AND M2.account = R.account -- or no active account at all?
                   AND M2.active  = 'active'
WHERE  M2.company IS NULL;

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. Remove AND M2.account = R.account from the JOIN condition if you want any.

Answer 2

select R.company, R.account, 'InActive' as Active
from M join R on R.company = M.company
group by R.company, R.account
having count(*) =1 and min(Active) = 'InActive'