[SOLVED] Table Comparison for record added or removed.

[suttercain]

Hi everyone,

 

I can't seem to wrap my head around a need of a current project. Let's save I have two tables, Table A and Table B, and all of the columns are identical. What I need to do is compare these two tables and find out if any of the Records were added or deleted since Table A, in table B.

 

TABLE A

----------

| column |

----------

| 0001    |

----------

| 0002    |

----------

| 0003    |

----------

| 0004    |

----------

 

TABLE B

----------

| column |

----------

| 0001    |

----------

| 0003    |

----------

| 0005    |

----------

 

So the report I would need to see, after comparing TABLE A against TABLE B is that, Records 0002 and 0004 have been dropped, and Record 0005 has been added.

 

Does anyone know the best way to approach this?

 

Thanks.

 

-SC

[artacus]

I don't use mysql enough to tell you if it does full outer joins. I don't believe it does. But you can do it like so.

 

SELECT id, 'inserted' AS action
FROM tablea
WHERE id NOT IN (SELECT id FROM tableb)

UNION ALL

SELECT id, 'deleted'
FROM tableb
WHERE id NOT IN (SELECT id FROM tablea)

[suttercain]

Artacus,

 

So far so good. I ran the query you provided and it echos 2 4 5. This is good because 2 and 4 were the numbers deleted and 5 was the number added.

 

My question would now be this, is there away to echo that 2 and 4 were the numbers deleted and that 5 was the number added?

 

Thanks!

[suttercain]

D'oh! Got it. Thanks

[artacus]

Didn't expect me to be a step ahead of you huh?