Phpmyadmin Query Userid Content

[brownstone4]

Hi,

 

So when a user (joomla) is deleted their name, userid is removed from the jos_community_users table. However I have some extensions that keep content from that deleted user. I want to run query to see which content is left behind so I can manually delete it. I had read that maybe a JOIN query should be run but I am not sure how to write the query.

 

Basically I need to compare for example the jos_blah_comments table which has row 'created by' with the userid numbers. I would like to compare this with jos_community_users row 'userid' and have the query show which comments have no user anymore.

 

Any help would be appreciated,

 

Thanks.

[Jessica]

Try like this

SELECT jos_blah_comments.fields 
FROM jos_blah_comments
LEFT JOIN jos_community_users
ON jos_community_users.userid = jos_blah_comments.userid
WHERE jos_blah_comments.userid IS NULL

 

If that doesn't work, you'll have to do a subquery to find all the userids and do NOT IN.

[brownstone4]

Thank you that helped. Got it to work by changing it a bit to

 

SELECT jos_blah_comments.created_by
FROM jos_blah_comments
LEFT JOIN jos_community_users
ON jos_community_users.userid = jos_blah_comments.created_by
WHERE jos_community_users.userid IS NULL

Edited December 27, 2012 by brownstone4