July 14, 2005 at 9:33 am
hello,
IM new to this whole sql 2000 and im having problems trying to merge tables.
*IE I have 2 tables with matches and its in order like
IDNUM1 matches IDNUM2
aaaaa bbbbb
bbbbb aaaaa
....and so on for table 1
but i also have another table with a differnt set of dat that goes the same way but they both have the same id numbers just they are comparing two differnt results. im trying to integrate the two and take out the duplicates and insert like a row or somehting that puts a check mark or somehting of that sort next to if both of them match or if only one match.
if u could be of some assistance to me i would be grateful
July 14, 2005 at 9:35 am
Can you post each table definition with some sample data and the exepected results of this work.. I don't have enough information to give you a definitive answer.
July 14, 2005 at 9:53 am
its classified. but let me see what i can do
i want it to look like this
id1 / (matches) id2/ fingerprint /face match /reasons
aaaa bbbb yes yes applying for job, got job
table 1 contains
id1 / (matches) id2/ fingerprint /reason
aaaa bbbb yes applying for job
bbbb aaaa yes got job
(they are the same person just with updated info)
and table 2 contains
id1 / (matches) id2/ facematch
aaaa bbbb yes
bbbb aaaa yes
combine the 2 but take of the extra set that matchs
like since it already has aaaa-matches bbbb then thier is no need to put bbbb-matches aaaa just the reason 4 it i hope that helps
July 14, 2005 at 12:31 pm
Can this help you?
Create table #A (id1 int not null, id2 int not null, primary key clustered (id1, id2))
Insert into #A (id1, id2) values (1, 2)
Insert into #A (id1, id2) values (2, 3)
Insert into #A (id1, id2) values (2, 1)
--no error because the server doesn't recognize 1,2 and 2,1 as the same pair
--find the pair :
Select A1.id1, A1.id2, A2.id1, A2.id2 from #A A1 inner join #A A2 on A1.id1 = A2.id2 and A2.id1 = A1.id2
DROP TABLE #A
GO
Create table #A (id1 int not null, id2 int not null, primary key clustered (id1, id2), CONSTRAINT CK_UniquePair CHECK ([id1] < [id2]))
Insert into #A (id1, id2) values (1, 2)
Insert into #A (id1, id2) values (2, 3)
Insert into #A (id1, id2) values (2, 1)
--fails because of the constraint and avoids any possible double
DROP TABLE #A
July 14, 2005 at 2:13 pm
thanks i will see if it works
July 14, 2005 at 2:23 pm
k.. I just hope I understood what you needed .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply