help!!!!!! tables

  • 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

  • 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.

  • 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

     

  • 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

  • thanks i will see if it works

     

  • 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