Select query between two tables

[sukatoa]

I need help on how to query this scenario

Suppose i have this tables:

TABLE1
+--------+-------+
| usr | value |
+--------+-------+
| werty1 | 789 |
| werty2 | 789 |
| werty3 | 1111 |
| werty4 | 3423 |
| werty5 | 234 |
| werty6 | 234 |
+--------+-------+

TABLE2
+--------+-------+
| usr | value |
+--------+-------+
| werty1 | 789 |
| werty2 | 789 |
| werty3 | 3423 |
| werty4 | 3423 |
| werty5 | 222 |
| werty6 | 333 |
+--------+-------+

I would like to get the result only to those usr and exist both on TABLE1 and TABLE2 that their value is different.

the expected output:

werty3 11111 3423
werty5 234 222
werty6 234 333

I still get stuck on how to query this stuff and as of now, im experimenting on this query hoping to gain new

select usr,value from TABLE1 where usr in (select usr from TABLE2) and value in (select values from TABLE2);

Any advise?

[SLauren]

You can try this:

SELECT    DISTINCT TABLE1.*
FROM        TABLE1
LEFT JOIN     TABLE2
ON        TABLE1.usr = TABLE2.usr AND TABLE1.value = TABLE2.value WHERE TABLE2.usr is null AND TABLE2.value IS NULL;

Hope this could help.

Thanks,
Lauren

[mwasif]

Try the following

SELECT usr,value FROM TABLE1 
INNER JOIN TABLE2 ON TABLE1.usr = TABLE2.usr
WHERE TABLE1.value <> TABLE2.value

[nbiswas]

Apart from the answers given here, you can also try the following queries

Query 1:

select t1.usr, t1.value, t2.value
from TABLE1  t1 
join TABLE2 t2
on t1.value  <> t2.value and t1.usr  = t2.usr 

Query2

select t1.usr, t1.value, t2.value
from TABLE1  t1 , TABLE2 t2
where t1.value  <> t2.value and t1.usr  = t2.usr

Query3

select  t1.usr, t1.value,t2.value from TABLE1  t1 , TABLE2 t2
where t1.value not in (select value from TABLE2)
and t1.usr  = t2.usr 

Query 4

select * from (
select  t1.usr, t1.value,
    (select t2.value from  TABLE2 t2
     where t1.value <> t2.value and t1.usr  = t2.usr) value2
from TABLE1  t1 )x where x.value2 is not null
 

Output(For all the cases)

usr    value    value
werty3    1111    3423
werty5    234    222
werty6    234    333

[sukatoa]

All of your replies are effective, thank you so much. appreciated :)