Select records from a single table that have common data in two columns
-
How can I select records from a single sql table that have common data in two of their columns. Thanks, i.e. table customer {Name, Address, Phone Number) How can I select all records from customer where address and phone number are the same even though the records are unique?
Ugly way that works: select distinct a.* from Customer a, Customer b where a.Address = b.Address and a.[Phone Number] = b.[Phone Number] and a.Name <> b.Name order by Address, [Phone Number], Name There must be a prettier way to do it...but at least it works in MS SQL. ;P
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
-
How can I select records from a single sql table that have common data in two of their columns. Thanks, i.e. table customer {Name, Address, Phone Number) How can I select all records from customer where address and phone number are the same even though the records are unique?
You could try:
SELECT *
FROM customer
WHERE Phone = AddressMy advice is free, and you may get what you paid for.
-
How can I select records from a single sql table that have common data in two of their columns. Thanks, i.e. table customer {Name, Address, Phone Number) How can I select all records from customer where address and phone number are the same even though the records are unique?
-
When done, it'd be wise to put a unique-constraint on that phone-number to prevent this from happening again.
Bastard Programmer from Hell :suss:
What happens if he wants to track 2 people at the same residence (and don't give me any crap about mobiles, not everyone has one).
Never underestimate the power of human stupidity RAH
-
How can I select records from a single sql table that have common data in two of their columns. Thanks, i.e. table customer {Name, Address, Phone Number) How can I select all records from customer where address and phone number are the same even though the records are unique?
Do a group by on the fields you need to test and a count.
Select count(*), Name,Phone,Address from SomeTable
Group By Name,Phone,Address
having count(*) > 1This will give you the dupes, then join the results back to the original table on these 3 fields to get the actual records. Standard deduping excercise.
Never underestimate the power of human stupidity RAH
-
What happens if he wants to track 2 people at the same residence (and don't give me any crap about mobiles, not everyone has one).
Never underestimate the power of human stupidity RAH
That would lead to another design; either it's correct to have multiple records or it isn't. Since he's asking how to identify them, I'm assuming that he doesn't want them. The end-user has to be able to identify wheter his/her item is in that list, and they need something to uniquely identify the tupel, otherwise they'll end up putting the data in there twice, creating redundancy. To answer your question; the telephone number shouldn't be an attribute of the residency. Without that, we wouldn't have to model database, would we? We'd simply hook up everything to a autonumber and we'd be done - but Access proves that it ain't that simple :)
Bastard Programmer from Hell :suss:
-
That would lead to another design; either it's correct to have multiple records or it isn't. Since he's asking how to identify them, I'm assuming that he doesn't want them. The end-user has to be able to identify wheter his/her item is in that list, and they need something to uniquely identify the tupel, otherwise they'll end up putting the data in there twice, creating redundancy. To answer your question; the telephone number shouldn't be an attribute of the residency. Without that, we wouldn't have to model database, would we? We'd simply hook up everything to a autonumber and we'd be done - but Access proves that it ain't that simple :)
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
he's asking how to identify them, I'm assuming that he doesn't want them.
See....I read it as probably a case of he wanted to find all the people who share a residence & phone. Traditional married is easy, based on just the last name and address, but people don't necessarily share names anymore. Thus address + phone number.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
-
Eddy Vluggen wrote:
he's asking how to identify them, I'm assuming that he doesn't want them.
See....I read it as probably a case of he wanted to find all the people who share a residence & phone. Traditional married is easy, based on just the last name and address, but people don't necessarily share names anymore. Thus address + phone number.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
No, you can't read it as you like; it depends on how it's used. If the table can contain two people on the same address, it would be holding people, not addresses. How can one uniquely identify a "human"? Well, the only way that the end-user can be sure, is by using a non-changing and unique attribute of the entity. The phone-number is not an attribute that uniquely identifies a person, nor a household. We've got normalization-rules for a reason :)
Bastard Programmer from Hell :suss:
-
No, you can't read it as you like; it depends on how it's used. If the table can contain two people on the same address, it would be holding people, not addresses. How can one uniquely identify a "human"? Well, the only way that the end-user can be sure, is by using a non-changing and unique attribute of the entity. The phone-number is not an attribute that uniquely identifies a person, nor a household. We've got normalization-rules for a reason :)
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
No, you can't read it as you like; it depends on how it's used.
;P After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time. Now, totally redesigning what he's working with is another thing, which should be addressed. :-D
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
-
Eddy Vluggen wrote:
No, you can't read it as you like; it depends on how it's used.
;P After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time. Now, totally redesigning what he's working with is another thing, which should be addressed. :-D
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
GenJerDan wrote:
After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Most people assume they can, which explains the high number of databases with redundant and even incorrect data. There wouldn't be a reason to search the dupes, if it had been analyzed correctly - it would have had a constraint to prevent them from being entered in the first place. :)
Bastard Programmer from Hell :suss:
-
GenJerDan wrote:
After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Most people assume they can, which explains the high number of databases with redundant and even incorrect data. There wouldn't be a reason to search the dupes, if it had been analyzed correctly - it would have had a constraint to prevent them from being entered in the first place. :)
Bastard Programmer from Hell :suss:
Hmm. Search for dupes. But they're not dupes as such. At least not in the sense of "I want to find the dupes to eliminate them". Maybe more like "I want to find the people who share an address so the company only has to send out one phone book to each household." Yes, you can do that with a normalized database, too. ;)
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
-
Hmm. Search for dupes. But they're not dupes as such. At least not in the sense of "I want to find the dupes to eliminate them". Maybe more like "I want to find the people who share an address so the company only has to send out one phone book to each household." Yes, you can do that with a normalized database, too. ;)
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
GenJerDan wrote:
Search for dupes. But they're not dupes as such.
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it? Your description doesn't sound very logical either; you're referring to clients, their addresses (some clients could have multiple, in theory), and their phones (could be multiple per location, or even independant of the location)
GenJerDan wrote:
Yes, you can do that with a normalized database, too.
I never stated that it wouldn't be :)
Bastard Programmer from Hell :suss:
-
GenJerDan wrote:
Search for dupes. But they're not dupes as such.
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it? Your description doesn't sound very logical either; you're referring to clients, their addresses (some clients could have multiple, in theory), and their phones (could be multiple per location, or even independant of the location)
GenJerDan wrote:
Yes, you can do that with a normalized database, too.
I never stated that it wouldn't be :)
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it?
Not to me. The way I read it is "Find records that have the same address and phone number, but different names", which does fit the share-a-house scenario.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
-
Eddy Vluggen wrote:
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it?
Not to me. The way I read it is "Find records that have the same address and phone number, but different names", which does fit the share-a-house scenario.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. My Mu[sic] My Films My Windows Programs, etc.
You shouldn't look at what fits, but at what's required. We model reality, you can't expect reality to follow your model. It might be true that there are multiple entities at an address, and it might be false - but any assumption is false by definition.
Bastard Programmer from Hell :suss:
-
You could try:
SELECT *
FROM customer
WHERE Phone = AddressMy advice is free, and you may get what you paid for.
No, that will return customers who have the same information stored in the phone and address fields of a single record, rather than two different customers who have the same phone number and the same address...
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Do a group by on the fields you need to test and a count.
Select count(*), Name,Phone,Address from SomeTable
Group By Name,Phone,Address
having count(*) > 1This will give you the dupes, then join the results back to the original table on these 3 fields to get the actual records. Standard deduping excercise.
Never underestimate the power of human stupidity RAH
No, this will only give you results where the name is also the same, not just the phone and address...
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
No, this will only give you results where the name is also the same, not just the phone and address...
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
True, removing the name will do the job then.
Never underestimate the power of human stupidity RAH
-
No, that will return customers who have the same information stored in the phone and address fields of a single record, rather than two different customers who have the same phone number and the same address...
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Okay, I see how I may have misinterpreted the requirement. I thought he wanted to find mistakes where say a phone number was filled into the address field.
My advice is free, and you may get what you paid for.