I have located a potential issue in the correlation between 2 tables and would like to correct it before it actually becomes one. (actually I need to fix it so the new scripting I'm doing will work correctly)
I have a member information table called 'members' (38,881 records)
I have a table called 'ref_track' that tracks who referred whom. (38,469 records)
the records consist of fields mem_id and ref_id - when a new member joins their ref_id is automatically recorded now.
(this wasn't important at first but has been added at some point, so does not contain records for all members)
I need it to contain a referrer for every member.
Here is the query I wrote to determine the members in the 'members' table that don't have a corresponding referrer listed in the 'ref_track' table
SELECT A.mem_id
FROM members as A
WHERE NOT EXISTS(
SELECT B.ref_id
FROM ref_track as B
WHERE A.mem_id=B.mem_id
)
That gives me a list of 486 records...
(This in itself is a problem because the difference between 38,881 and 38,469 is only 412, not 486)
2 Questions...
1) what is wrong with the query? The number of referral records should be 1 less than the number of members.
2) How can I convert this query (once corrected) to be used to insert records into the ref_track table for each of the members mem_id records that don't currently exist.
Any feedback you could give me on either, or both questions, would be greatly appreciated.
Thanks,
Douglas