Help w/ duplicate rows query

[bbwrede]

HI,
I am having trouble with a query. I am selecting from 1 database.

Equipment Table

phone#
equipcode

I want all rows that have the same equipcode for one phone #. For example

equipment table
phone # equipcode
404-234-1232 DSL
404-234-1232 DSL
404-234-1232 XVU

I should get 404-234-1232 because that # has DSL twice. I have tried the following query:

select phone# from equipment eq1, equipment eq2
where
eq1.phone = eq2.phone and eq1.equipcode = eq2.equipcode
I of course am matching on the same record in the 2nd table. I don't have any unique field to clear out the exact same record in the other table.

Thanks for any help!
Barb

 

[ClaireHCM]

I dont really understand your requirement.

Cant it be accomplished by the following query

select distinct phone from equipment

If this doesnt match what you want ,could you list your desired output?

 

[bbwrede]

Thanks for answering. I will show you a more in-depth example.

equipment table
phone # equipcode
404-234-1232 DSL
404-234-1232 DSL
404-234-1232 XVU
404-234-5467 DSL
404-234-4000 XVU
404-234-4000 DSL
404-234-4000 XVU
404-234-4005 DSL
404-234-4005 XVU

select all rows that have the same phone # and the same equipment code.

The result should be
404-234-1232 DSL
404-234-4000 XVU

Those 2 phone #'s have duplicate equipment codes in the table. I want only the records that have the same equipment code more than once on the same phone #. You can think of it this way: what customers are being charged twice for the same service. We don't want more than one record of the same phone # with the same equipment code. You don't want to pay for dsl twice. I hope this explains it I am obviously a little confused myself.
Thanks for the help.

 

[swampBoogie]

select phone#,equipcode
 from equipment
 group by phone#,equipcode
 having count(*) > 1

 

[bbwrede]

Thanks!!