March 17, 2006 at 1:51 am
Guys hi,
i have the following problem. I have two databases, that contain the same records. The databases are about candidates for exams. The first database contains more candidates that the second as it is updated , but the second contains [some times] more info about candidates.
From both databases i want to retrieve the results and then combine them in order to fill as many [complete] data as possible. For example, from the first database i get the following record.
userlogin address1 address2 telephone zipcode
aadamx02 123 avenue New york 07802070707
from the second database i get the same record only i dont have the telephone but the zipcode instead. For example.
userlogin address1 address2 telephone zipcode
aadamx02 123 avenue New york co34sq
Obviously when i join the queries results [union all] i get two records for each userlogin. However i want to get only ONE record containing the complete address info. That is i want the following
userlogin address1 address2 telephone zipcode
aadamx02 123 avenue New york 07802070707 co34sq
how can i do this? any help?
Thank you for your time reading this. Your help would be valuable!
March 17, 2006 at 2:47 am
Hi Dionisis,
Something like this perhaps?
--This SQL script is safe to run
declare @t1 table (userlogin varchar(30), address1 varchar(30), address2 varchar(30), telephone varchar(30), zipcode varchar(30))
insert @t1 values ('aadamx02', '123 avenue', 'New york', '07802070707', null)
insert @t1 values ('a', null, null, '07802078888', null)
declare @t2 table (userlogin varchar(30), address1 varchar(30), address2 varchar(30), telephone varchar(30), zipcode varchar(30))
insert @t2 values ('aadamx02', '123 avenue', 'New york', null, 'co34sq')
insert @t2 values ('a', '42 avenue', 'New york', '043733233', null)
select
isnull(a.userlogin, b.userlogin) as userlogin,
isnull(a.address1, b.address1) as address1,
isnull(a.address2, b.address2) as address2,
isnull(a.telephone, b.telephone) as telephone,
isnull(a.zipcode, b.zipcode) as zipcode
from
@t1 a
full outer join @t2 b on a.userlogin = b.userlogin
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
March 17, 2006 at 3:48 am
March 17, 2006 at 3:53 am
I might be missing the point here, but a simple union query can do this
select * from server1.mydatabase.dbo.mytable
UNION
select * from server1.mydatabase.dbo.mytable
all the results are combined and issued as a single recordset with the duplicates removed
MVDBA
March 17, 2006 at 4:02 am
Michael,
you are missing the point. Doing your suggestion, i get two records for the same user. This is because in one database i have all the user's info (eg telephone1) except the zipcode, and in the second database i have the same user, with no telephone data, but WITH the zip code. The union returns two records as they are different! 🙂
Thank you for the time to answer my question 🙂
March 17, 2006 at 4:10 am
Ah,
i see
in that case might i suggest
select a.userlogin, isnull(a.address1,b.address1),isnull(a.zipcode,b.zipcode)..... from database1 a inner join database2 b on a.userlogin=b.userlogin
might be a little simpel, but it depends on having nulls in the columns and not ' '
MVDBA
March 17, 2006 at 5:09 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply