Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
2  
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

2 Answers 2

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.

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.

share|improve this answer
1  
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
1  
@Jackofall: Right, you want inactive ones, amended my answer. Cool that it works. :) –  Erwin Brandstetter Apr 24 '12 at 19:24
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'
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

 
discard

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.