Database

Re: Select records from a single table that have common data in two columns

13-Jun-11 11:11

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. Poke tongue | ;-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.

Johan Hakkesteegt13-Jun-11 22:40

Johan Hakkesteegt

13-Jun-11 22:40

You could try:

SELECT *
FROM customer
WHERE Phone = Address

My advice is free, and you may get what you paid for.

Re: Select records from a single table that have common data in two columns

_Damian S_14-Jun-11 14:54

_Damian S_

14-Jun-11 14:54

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

Re: Select records from a single table that have common data in two columns

Johan Hakkesteegt14-Jun-11 21:26

Johan Hakkesteegt

14-Jun-11 21:26

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.

Re: Select records from a single table that have common data in two columns

Eddy Vluggen14-Jun-11 1:10

Re: Select records from a single table that have common data in two columns

14-Jun-11 1:10

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 Suspicious | :suss:

Mycroft Holmes14-Jun-11 1:22

Mycroft Holmes

14-Jun-11 1:22

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

Re: Select records from a single table that have common data in two columns

Eddy Vluggen14-Jun-11 1:29

Re: Select records from a single table that have common data in two columns

14-Jun-11 1:29

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

GenJerDan14-Jun-11 4:05

Re: Select records from a single table that have common data in two columns

14-Jun-11 4:05

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 Vluggen14-Jun-11 4:12

Re: Select records from a single table that have common data in two columns

14-Jun-11 4:12

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

GenJerDan14-Jun-11 4:29

Re: Select records from a single table that have common data in two columns

14-Jun-11 4:29

Eddy Vluggen wrote:
No, you can't read it as you like; it depends on how it's used.

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. Big Grin | :-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 Vluggen14-Jun-11 4:44

Re: Select records from a single table that have common data in two columns

14-Jun-11 4:44

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.

Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

GenJerDan14-Jun-11 4:58

Re: Select records from a single table that have common data in two columns

14-Jun-11 4:58

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. Wink | ;)

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 Vluggen14-Jun-11 5:20

Re: Select records from a single table that have common data in two columns

14-Jun-11 5:20

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

GenJerDan14-Jun-11 5:34

Re: Select records from a single table that have common data in two columns

14-Jun-11 5:34

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 Vluggen14-Jun-11 7:07

Re: Select records from a single table that have common data in two columns

14-Jun-11 7:07

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 Suspicious | :suss:

Mycroft Holmes14-Jun-11 1:25

Mycroft Holmes

14-Jun-11 1:25

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(*) > 1

This 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

Re: Select records from a single table that have common data in two columns

_Damian S_14-Jun-11 14:55

_Damian S_

14-Jun-11 14:55

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

Re: Select records from a single table that have common data in two columns

Mycroft Holmes14-Jun-11 19:53

Mycroft Holmes

14-Jun-11 19:53

True, removing the name will do the job then.

Never underestimate the power of human stupidity
RAH

checking the fieldname in the table

Dhyanga13-Jun-11 9:42

Dhyanga

13-Jun-11 9:42

Hi,
I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info.
For example
table field may be
studentID
studentName
studentDetails

or
ID
studentName
studentDetails

or
ID
student_Id
studentName
studentDetails

so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?

suchita

PIEBALDconsult13-Jun-11 14:48

PIEBALDconsult

13-Jun-11 14:48

I'd use an EnumTransmogrifier<T>
[^].

Make an enum like:

enum ColumnName
{
    [System.ComponentModel.DescriptionAttribute("ID")]
    [System.ComponentModel.DescriptionAttribute("student_Id")]
    studentID

etc.
    studentName

etc.
    studentDetails
}

Then, when you open a file, parse the actual column names to a logical column names and cache the indices.

Slick as snot.

Mohamad Kaifi13-Jun-11 23:53

Mohamad Kaifi

13-Jun-11 23:53

Hi Suchita,
You can get column names in SQl Server using the following query:

--This will give you all the column names in the customer table
Select COLUMN_NAME From <Your Database Name>.INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'Customer'

Hope this solves your problem.

Thanks,
Kaifee

PIEBALDconsult14-Jun-11 2:53

PIEBALDconsult

14-Jun-11 2:53

Will that work with Excel?

Using a DataReader is simpler and works with any ADO.net-compatible database.

Dhyanga14-Jun-11 3:10

Dhyanga

14-Jun-11 3:10

Can I get the same output if I have an excel file ? Actually I want the data from the excel file and insert it into the sql server. Is there anyway I can get the same output with the excel file?

suchita

Simon_Whale14-Jun-11 3:04

Simon_Whale

14-Jun-11 3:04

Have you thought about using the openrowset functionality of SQL Server? and letting SQL check to see if the values in the spreadsheet are in the database? rather than Excel doing the work!

MSDN: Openrowset[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch