Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Have you tried a JOIN? Or using the EXISTS? – ypercubeᵀᴹ Apr 23 '12 at 20:04
that's what I have been trying for the past hour. – Jackofall Apr 23 '12 at 20:15
up vote 4 down vote accepted

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.

              AND M1.account = R.account
              AND  = 'inactive'
                   AND M2.account = R.account -- or no active account at all?
                   AND  = 'active'

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.

share|improve this answer
The AND M2.account = R.account may need to be removed. OP's sample suggests it should be there. The explanation suggests the opposite. – ypercubeᵀᴹ Apr 23 '12 at 21:16
+1 It worked. There is is s light mistake. The first active ='active' should be 'inactive' and the second active to return correct result. The query as it is gives me all active only records. – Jackofall Apr 24 '12 at 13:04
@Jackofall: Right, you want inactive ones, amended my answer. Cool that it works. :) – Erwin Brandstetter Apr 24 '12 at 19:24
select, R.account, 'InActive' as Active
from M join R on =
group by, R.account
having count(*) =1 and min(Active) = 'InActive'
share|improve this answer
Bear in mind that I can have 2 or 3 inactive accounts with no active. Will it catch those? – Jackofall Apr 24 '12 at 12:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.