Returning Distinct Values 1

[lrdave36]

Hey guys,

I am trying to design a query that will do the following:

Return employees who have both N and K plan code values in their history table.

Sample table


SSN DATE EMPR_PLAN_CD
____ ____ _____________

11233333 2003-04-01 N
11233333 2005-05-01 K


In this example, the SSN would appear in my results because they have both N and K.

I came up with this query which does appear to work:

select * from HISTORY A
where a.empr_plan_cd = 'N'
 and exists (select 1 from  HISTORY B
             where B.SSN = A.SSN
             and B.empr_plan_cd = 'K')

My problem is when I tack this code onto my larger more complex query, SQL bogs down, and never stops processing.

Is there another more efficient method to return rows with distinct values for the same SSN? (SSN is the primary key in the table) Thanks!

 

[gmmastros]

Try this:

select A.* 
from   HISTORY A
       Inner Join (
         Select SSN
         From   HISTORY
         Group BY SSN
         Having Count(Case When empr_plan_cd = 'N' Then 1 End) > 0
                And Count(Case When empr_plan_cd = 'K' Then 1 End) > 0
         ) As B
         On A.SSN = B.SSN

If this query returns the correct data and you want me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

This is a problem of relational division. You may want to review this interesting article by PESO (Peter Larsson)

http://sqlblog.com/blogs/peter_larsson/archive/2010/06/30/relational-algebra.aspx

PluralSight Learning Library