Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmoneyless
Starting Member
2 Posts |
 Posted - 2007-10-22 : 16:13:57
|
| this is my table below, I want to get a result that gives me all the names as a unique by lowest id and then look through the records to get thier address, city, state, and zip. So if they have any info in any of the records, it will put it into one record for that person with as much info that is available. I also made a table that has just one instance of each name. I tried query below but i don't know what to look for to get info from other records.SELECT fl.fname,fl.lname,fl.address,fl.city,fl.state,fl.zip,fl.id FROM firstLast flinner join [unique] u on u.name = fl.fullname ORDER BY lname DESCid fname lname address city state zip---- ------ ------ ------------- ----------- -- -----1031 Donald Zudweg Moline MI 493359360 Willem Zierse 1234 test way 3130 Willem Zierse West Valley UT 841289318 Terrell Ziegler 70 Andrea Zastawny 8796 Andrea Zastawny 480 Andrea Zastawny ROCHESTER MI 483071123 Andrea Zastawny ROCHESTER MI 48307154 Andrea Zastawny ROCHESTER MI 483076569 richard zancker 3081 richard zancker BOISE ID 837097904 Alice Zamora 5678 test way 10395 Alice Zamora 1722 Alice Zamora San Antonio TX 782518597 Mark Zamora 2570 Mark Zamora NAMPA ID 83686the result set would be this result setid fname lname address city state zip---- ------ ------ ------------- ----------- -- -----1031 Donald Zudweg Moline MI 493353130 Willem Zierse 1234 test way West Valley UT 8412870 Andrea Zastawny ROCHESTER MI 483073081 richard zancker BOISE ID 837091722 Alice Zamora 5678 test way San Antonio TX 782512570 Mark Zamora NAMPA ID 83686 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-10-22 : 21:37:35
|
| First, this is a dangerous thing to do to your data.What if you have two records for a person, the first of which has a valid Street Number and City for one address, and the second one has only a State and Zip Code? Merge the two, and you end up with a record that has a completely invalid address.Regardless, you could do what you want by using an aggregate query, but you should really treat all the address fields as an atomic unit even if they are in separate columns. The question then becomes, if a person has two distinct and valid addresses, what business logic do you want to apply to choose between them. The "first" chronologically"? The "latest"? The "most complete"?e4 d5 xd5 Nf6 |
 |
|
|
dmoneyless
Starting Member
2 Posts |
Posted - 2007-10-23 : 12:43:36
|
| I understand the problem, I got this file from my bosses and I think they just through it together from an old database. I think they want to get the most data possible. I would like to fill as much info as possible and when you need to make a decision about which data to use, the determining factor would be the id. I had originally asked for it to be the lowest id, but I think the better way would be to use the higher id, thinking that that was put in - or updated - after the the lower id was in there. Would there be a way to fill a new table with as much info as possible and then mark or flag the ones that have more than two addresses. If there are two, then use the one with the higher id. Then I could go through manually to choose the data for ones with more than three valid addresses. I am new too queries like this that need to use logic. Any help is greatly appreciated. |
|
|
|
|
|
|
|
|
help with duplicates and empty fields