Comparing 2 tables for differences

[srarcade]

Hello- Trying to seperate data differences between 2 tables. Lets say i have 2 tables like the following

tbl_1
int_id - str_name
1 - Bob
2 - Roger
3 - Kim
4 - Jane

tbl_2
int_id - str_name
1 - Bob
2 - John
3 - Kim
4 - George

How could i construct a query to get results of the differences? Like seeing what names are in tbl_2 that are not in tbl_1? Or what is not in tbl_2 that is in tbl_1?

Any help appreciated! Thanks.

[raf]

Welcome here!

I'm not completely sure wha you are asking for.
Do you want to get the rows where the id-name combination is different? or just all names that don't occur in both tables?

so if
tbl_1
int_id - str_name
1 - Bob
2 - Roger
3 - Kim
4 - Jane

and

tbl_2
int_id - str_name
1 - Jane
2 - Roger
3 - Bob
4 - Jack

what shoukd then be the result?

[srarcade]

ok im looking for just the names

so query1 would be 'select just names that appear in table 2' and it would produce the output of:
tbl_2
John
George

query2 would be the same thing, except with table 1 only names instead.

[raf]

sorry, but you're not realy clear in your explanation.

do you want all names that only occur in one of the tables?

[srarcade]

ok like you said is correct, im trying to have my query result print just the names that only appear in table 2. Any names in table 1 that are also in table 2 would be ommitted. Ive done this before a very long time ago, i just cant remember how i did it.

[raf]

well, the easiest way is using a subquery:

select str_name from table2 where strname Not In (select str_name from table1)

but you can only use subselects in mysql v4.1 and up.
if you have an older version, then you need to use an outer join.

select table2.str_name FROM table2 LEFT JOIN table1 ON table2.str_name=table1.str_name WHERE table1.str_name IS NULL;

The str_name columns should then be defined as NOT NULL !

what version do you have?

[srarcade]

I have version 4.0.18 :( The JOIN query works great, gives me just what I needed. Thanks alot for you help and patience!

[raf]

you're welcome :thumbsup: glad i could help.