Btukfyl

Gday,

We have two tables that contain exactly the same structure. There are two columns "PrimaryAddress" and "AliasAddress". These are for email addresses and aliases. We want to find any records that need to be added to either side to keep the records in sync. The catch is that the primary name in one table might be listed as an alias in the other. The good news is that an address wont appear twice in the "AliasAddress" column.

TABLE A

PrimaryAddress~~~~~AliasAdress

chris@work~~~~~~~~~chris@home

chris@work~~~~~~~~~c@work

chris@work~~~~~~~~~theboss@work

chris@work~~~~~~~~~thatguy@aol

bob@test~~~~~~~~~~~test1@test

bob@test~~~~~~~~~~~charles@work

bob@test~~~~~~~~~~~chuck@aol

sally@mars~~~~~~~~~sally@nasa

sally@mars~~~~~~~~~sally@gmail



TABLE B

PrimaryAddress~~~~~AliasAdress

chris@home~~~~~~~~~chris@work

chris@home~~~~~~~~~c@work

chris@home~~~~~~~~~theboss@work

chris@home~~~~~~~~~thatguy@aol

bob@test~~~~~~~~~~~test1@test

bob@test~~~~~~~~~~~charles@work

sally@nasa~~~~~~~~~sally@mars

sally@nasa~~~~~~~~~sally@gmail

sally@nasa~~~~~~~~~ripley@nostromo

The expected result is to return the following missing records from both tables:

bob@test~~~~~~~~~~~chuck@aol

sally@nasa~~~~~~~~~ripley@nostromo

Note that the chris@* block is a total match because the sum of all the aliases (plus primary) is still the same regardless of which address is considered primary. It doesnt matter which address is primary as along as the sum of the entire primary group contains all entries in both tables.

I dont mind if this is run in two passes A->B and B->A but I just cant get my head around a solution.

Any help appreciated :)

drop TABLE #TABLEA

CREATE TABLE #TABLEA

    ([PrimaryAddress] varchar(10), [AliasAdress] varchar(12))

;



INSERT INTO #TABLEA

    ([PrimaryAddress], [AliasAdress])

VALUES

    ('chris@work', 'chris@home'),

    ('chris@work', 'c@work'),

    ('chris@work', 'theboss@work'),

    ('chris@work', 'thatguy@aol'),

    ('bob@test', 'test1@test'),

    ('bob@test', 'charles@work'),

    ('bob@test', 'chuck@aol'),

    ('sally@mars', 'sally@nasa'),

    ('sally@mars', 'sally@gmail')

;





drop TABLE #TABLEB

CREATE TABLE #TABLEB

    ([PrimaryAddress] varchar(10), [AliasAdress] varchar(15))

;



INSERT INTO #TABLEB

    ([PrimaryAddress], [AliasAdress])

VALUES

    ('chris@home', 'chris@work'),

    ('chris@home', 'c@work'),

    ('chris@home', 'theboss@work'),

    ('chris@home', 'thatguy@aol'),

    ('bob@test', 'test1@test'),

    ('bob@test', 'charles@work'),

    ('sally@nasa', 'sally@mars'),

    ('sally@nasa', 'sally@gmail'),

    ('sally@nasa', 'ripley@nostromo')

;

try the following

select a.PrimaryAddress,a.AliasAdress  from #TABLEA a left join #TABLEB b on a.AliasAdress=b.AliasAdress or b.PrimaryAddress=a.AliasAdress 

where b.PrimaryAddress is null

union all

select a.PrimaryAddress,a.AliasAdress  from #TABLEB a left join #TABLEA b on a.AliasAdress=b.AliasAdress or b.PrimaryAddress=a.AliasAdress 

where b.PrimaryAddress is null

So you want to compare table A and B, and find rows which are unqiue in either table. How about an outer join, followed by looking for NULL values:

SELECT ta.*, tb.*



FROM       table_a ta



FULL OUTER JOIN table_b tb ON  tb.PrimaryAddress = ta.PrimaryAddress 

                           AND tb.AliasAddress = ta.AliasAddress



WHERE ta.PrimaryAddress IS NULL

   OR tb.PrimaryAddress IS NULL

If I understand the question correctly, this should return what you ask for.

Here's how I did it, with a bit of throwing-hands-up-in-the-air at the end.

Step one, identify the sets of items to be compared. This is:

• For a “primary” value, all values found in Alias


• Including the “primary” value as well (to cover that nasa/nostromo case)

A set in a table (A or B) is identified by its primary value. What really makes it hard is that the primary value is not shared across the two tables (sally@mars, sally@nasa). So we can compare sets, but we have to be able to “go back” to the primary on each table separately (e.g. the stand-out from table B may be sally@nasa / ripley@nostroomo, but we have to add sally@mars / ripley@nostromo to table A)

Major problems arise if, in a table, a primary value appears as an alias for a different primary value (e.g. in table A, chris@work appears as an alias for bob@test). For the sake of sanity, I am going to assume this will not happen… but if it does, the problem becomes even harder.

This query works to add missing items in B that are not in A, where the PrimaryAddress is the same for both A and B:

;WITH setA (SetId, FullSet)

 as (--  Complete sets in A

     select PrimaryAddress, AliasAdress   

      from A

     union select PrimaryAddress, PrimaryAddress

      from A

    )

,setB (SetId, FullSet)

 as (--  Complete sets in B

     select PrimaryAddress, AliasAdress   

      from B

     union select PrimaryAddress, PrimaryAddress

      from B

    )

,NotInB (Missing)

 as (--  What's in A that's not in B

     select FullSet

      from setA

     except select FullSet  --  This is the secret sauce. Definitely worth your time to read up on how EXCEPT works.

      from setB

    )

--  Take the missing values plus their primaries from A and load them into B

INSERT B (PrimaryAddress, AliasAdress)

 select A.PrimaryAddress, nB.Missing

  from NotInB nB

   inner join A

    on A.AliasAdress = nb.Missing

Run it again with the tables reversed (from “NotInB” on) to do the same for A.

HOWEVER

Doing so with your sample data for "in B not in A" will add (sally@nasa, ripley@nostromo) to A, and as that’s a different primary, it’d create a new set, and so does not solve the problem. It gets ugly quickly. Talking it out from here:

• Takes two passes, one for A not in B, one for B not in A


• For each pass, have to do two checks


• First check is what’s above: what’s in A not in B where primary addresses match, and add it


• Second check is ugly: what’s in A not in B where the primary addresses from A is NOT a primary address in B and, thus, must be an alias. Here, find A’s primary address in B’s alias list, get the primary key used for this set in B, and create the row(s) in B using those values.

OK, This is how we did it... As it was becoming a pain, we ran a procedure that added the primary address of each entry as an alias: xx@xx -> xx@xx so that all addresses were listed as aliases for each user. This is similar to what @Phillip Kelly did above. Then we ran the following code: (its messy but it works; in one pass too)

SELECT 'Missing from B:' as Reason, TableA.[primary] as APrimary, TableA.[alias] as AAlias, TableB.[primary] as BPrimary,TableB.[alias] as BAlias into #A FROM dbo.TableA LEFT OUTER JOIN TableB ON TableB.alias = TableA.alias 

SELECT 'Missing from A:' as Reason,TableA.[primary] as APrimary, TableA.[alias] as AAlias, TableB.[primary] as BPrimary,TableB.[alias] as BAlias into #B FROM dbo.TableB LEFT OUTER JOIN TableA ON TableA.alias = TableB.alias 



select * from #A

select * from #B



UPDATE #A 

   SET #A.APrimary = #B.BPrimary 

   FROM #B  INNER JOIN  #A ON #A.APrimary = #B.BPrimary

   WHERE #A.BPrimary IS NULL



UPDATE #B 

   SET #B.BPrimary = #A.APrimary 

   FROM #B  INNER JOIN  #A ON #B.BPrimary = #A.BPrimary

   WHERE #B.APrimary IS NULL



select * from #A

select * from #B



select * into #result from (

select Reason, BPrimary as [primary], BAlias as [alias] from #B where APrimary IS NULL

union

select Reason, APrimary as [primary], AAlias as [alias] from #A where BPrimary IS NULL

) as tmp



select * from #result



drop table #A

drop table #B

drop table #result



GO