query to create missing data records

[showman13]

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

[pritaeas]

1) There may be duplicate entries for an existing user referral.
2) You can use this SELECT in an INSERT INTO, but it depends on what you want to insert as referral.

[showman13]

Hmmm Well, maybe I don't have the query structured correctly then...

there is only 1 mem_id per member record, and this is an auto increment/unique field in the members table

the ref_track table should hold 1 record to match each record in the members table, and have a corresponding ref_id for each mem_id. There should never be more than 1 ref_ids assigned to a single mem_id.

I am simply trying to figure out which mem_ids are missing from the ref_track table,
And then
Be able to insert records for each of them that are missing, and assign a ref_id to them.

Maybe that is a better way to explain it.

[WayneZ]

While each mem_id has only one ref_id, that doesn't mean there is only one mem_id for each ref_id. Someone could have referred more than one member so several mem_id values could be linked to the same ref_id. Also, if a member has no ref_ID value, how do you know what ref_id to give them?

[showman13]

We haven't decided yet on how to assign a referrer, but it will either be a member record that will be like an Angel position, that takes all of the earnings and automatically reinvests in benefactoring positions, or we may decide to just randomly select other member records as the referrer... Either way, that won't have any bearing on finding the solution to the question.

The fact that the are multiple mem_ids with the same ref_id shouldn't have any effect on the outcome either, at least that was my intention, because I know that there are members with hundreds of personal referrals.

The query should basically just be stepping through the members table and comparing it to the ref_track table to find the mem_ids that are in the members table that don't exist in the ref_track table... so one mem_id to many ref_ids shouldn't matter.

[WayneZ]

If there is a one-to-one relationship between member records and referrer records, could you add the ref_id value to the member table and not keep it in a separate table? Or is there other information in the referrer table that is best kept separate?

[showman13]

Thanks for the feedback WayneZ...

That was actually my initial thought when I added the referral tracking, but that would have required making changes to every script that accessed the members table, which I didn't want to do at the time... In retrospect, I wish I had done that, but it would take a major rewrite of much of the logic...

Actually, at this point I have found a way to program around the issue, by assigning a default referrer for their initial position placement, and disallowing them to go any further without defining their actual referring member.

I'm pretty sure when I get past this major portion of the scripting and get them to the point of being operational, I will look at doing a complete rewrite of the system in the background, and do it all the way it should have been done in the beginning, like I would have done if I had all the information to begin with.

Thanks to everyone for your feedback.

Douglas