Comparing 2 tables and display missing rows

Question

Recently my joomla 1.5 website got targeted by spam registrations, so I went to phpmyadmin and start filtering and deleting those registrations from jos_users, but I totally forgot that joomla registration have 2 more tables dependencies (jos_core_acl_groups_aro_map and jos_core_acl_aro ) so now I have jos_users with 90.000 rows and the other 2 dependency tables with 120.000 rows.

I found this SQL command to populate the 3 tables:

insert into  jos_core_acl_aro (section_value, value, name)
    select 'users', id, name from jos_users;

But I found this too risky to use on a live site with 120.000 registrations and also it will restore the spam registrations i already deleted in jos_users

I need a SQL command to compare jos_users table with the other 2 tables and display the missing rows in jos_core_acl_groups_aro_map and jos_core_acl_aro which are the ones i deleted from jos_users.

I have tried this command:

select * 
from tcpt_users 
where tcpt_users.id not in (select tcpt_core_acl_groups_aro_map.aro_id
                            from tcpt_core_acl_groups_aro_map)

But this doesn't work and it makes mysql scream for memory and my server will crash.

Some directions will be mostly welcome.

Edit:

Ok, I got the alias concept you shared so I ran the follow command:

select * 
from tcpt_users a
left join tcpt_core_acl_aro b on a.id = b.value 
where b.value is null

But still drives mysql server crazy which impacts my server load starting from the beginning that the command is well formatted and I might have some server limitations is there a way to limit the number of rows to be displayed?

Edit 1: I think I figured it out the rows limit and this is what I ran:

select * 
from tcpt_users a
left join tcpt_core_acl_aro b on a.id = b.value 
where b.value is null
LIMIT 25

It happens the same i have to restart mysql because the query will last forever and my server freezes.

I'm out of options at this moment and i could use some help right now

Answer 1

you can use left join

select * from tcpt_users a
left join tcpt_core_acl_groups_aro_map b on a.id =b.aro_id 
where b.aro_id is null