Hiding duplicates

[jdwm2310]

hello,

I have a query of customers that have benefit plans. Some of these customers have two sets of benefits which means that their info will appear twice. However there are some customers that don't have any benefits. I want to see those customers and hide the customers that have duplicates. For example:

I want to hide information that are duplicated
John Smith 0123456789 MetLife
John Smith 0123456789 Vision

Any suggestion will be appreciated. thanks

 

[rajeessh]

Is Metlife and Vision are Benefit plabns

Assume CustomerName and Number to be selected


For Customers without benefit:
Select CustomerName,Num from Customer where
IsNull(BenPlan) or IsEmpty(BenPlan) Or Len(BenPlan) = 0

For Customers with benefit:
Select CustomerName,Num from Customer where
Not IsNull(BenPlan) And Not IsEmpty(BenPlan) And Len(BenPlan) > 0


For Customers With /Without Benefits

Select distinct CustomerName,Num,Len(BenefitName)> 0 from
Customer

The 3rd will be true or false according to benefit available or not.

 

[CharlesH]

Another more roundabout way to do it is to:

1. Run a query finding duplicate entries by name, grouping by customer names, and not including the other information. This gives a unique set of names of people with multiple entries.

2. Run a second unmatched query with the previous query linked to the original dataset by names to extract those people's records that are not included in the previous query. This second query should extract people's records that have only one entry.