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.
Best Answer
That should give you what you want: