Compare two tables with same structure, but differing membership numbers

Question

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below.

The databases are from the current and previous versions of a website. I need a query that searches all three tables for the names and membership numbers, compares the ids and membership numbers, then only displays the records that have membership numbers that do not match. I also need to exclude '0' as an unmatched result.

This matching only needs to happen between the two identically structured tables. The third table is only used to connect a unique name to each of the records returned from the two membership number tables. I have included my updated SQL query below.

SELECT 
 u.id, 
 u.name, 
 a.membership_no, 
 j.membership_no 
FROM 
 n2z7m3_users AS u 
LEFT JOIN n2z7m3_kiduka_accounts AS a ON (u.id = a.membership_no) 
LEFT JOIN n2z7m3_kiduka_accounts_j15 AS j ON (
 a.membership_no = j.membership_no
) 
WHERE a.membership_no NOT IN (
 SELECT 
 j.membership_no 
 FROM 
 n2z7m3_kiduka_accounts_j15 AS j
);

Users table structure and sample data:

+------+----------------+
| id | name |
+------+----------------+
| 6000 | Dave Sanderson |
+------+----------------+
| 6001 | Bill Willis |
+------+----------------+
| 6002 | John Hancock |
+------+----------------+

Membership numbers table structure and sample data from accounts table one:

+--------------------------------+
| id | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000 | 6000 |
+----------------+---------------+
| 6001 | 6001 | 6001 |
+----------------+---------------+
| 6002 | 6002 | 6002 |
+----------------+---------------+

Membership numbers table structure and sample data from accounts table two:

+--------------------------------+
| id | user_id | membership_no |
+----------------+---------------+
| 6000 | 6000 | 6010 |
+----------------+---------------+
| 6001 | 6001 | 6020 |
+----------------+---------------+
| 6002 | 6002 | 6030 |
+----------------+---------------+

The membership_no=6010 from the second accounts table would be the difference from the first accounts table. The user_ids are the same, but the membership_nos are different. This is what I am needing to find. All of the instances where the membership_nos are different using the ids from the users table and user_ids from the accounts tables.

Answer 1

That should give you what you want:

SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table' 
FROM T1
INNER JOIN UT
ON T1.user_id = UT.user_id 
INNER JOIN T2
ON T1.user_id = T2.user_id and T1.membership_no != T2.membership_no