Need to compare 2 tables.

ghosttr

I have two tables that have data that needs to be compared. The only thing the tables have in common is a userid tag. The first being a list of users, the second being a log which records certain events. I am wanting to compile a list of users from these logs and have setup a second database for the task. (So i cant fuck up the original)

Now here is where it gets tricky the first table is sorted by the userid, and each userid is only listed once. In the 2nd table users may appear multiple times, and the table is sorted dy date.

I thought this would work, but it just seems to fail silently

"DELETE FROM <table1>.userid WHERE <table2>.userid != <table1>.userid"

Anyone have any ideas?

stolzyboy

you don't need the <table1>.userid as that would be a column, give it a whirl without that, like:

DELETE FROM <table1> WHERE <table2>.userid != <table1>.userid

ghosttr

No, that didnt work, everything seems to be setup ok, but when i go back an query the db nothing has changed at all.

Here is the full php script, i feel that it should be working but nothing seems to happen.

<?

$username="<username>";
$password="<password>";
$database="<database>";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="DELETE FROM <table1>WHERE <table1>.userid != <table2>.userid";
$result=mysql_query($query);

mysql_close();


?>

Here an example of what im trying to reference.

<table1>
userid
102441019
102441119
102441219
102441319
102441719

<table2>
userid
102441019
102441019
102441319
102441319
102441119

<Table1> being an index of all registered users, and table 2 being a log that records every time a user does a specific action.
So all userid entries that are in <table1> are in <table2>, but not all user participated so not all userids are present in <table2>, and some people participated more than once there may be some duplicate entries in <table2>.

laserlight

I am wanting to compile a list of users from these logs and have setup a second database for the task. (So i cant fuck up the original)

I do not really see the point. Backup the database if you want to be able to recover from an accident.

If you want to find all the users who were logged, then a DELETE does not make sense. You really want a SELECT DISTINCT from the log table.

ghosttr

All I need to do is get the shortened list of users. I am trying to delete inactive users without manually going in and removing every entry.

SELECT DISTINCT works fine, but I still need to have my database updated, and keep the userdata in the first table intact for the user entries that aren't being deleted.

laserlight

Ah, perhaps a subquery would do the trick:

DELETE FROM <table1> WHERE userid NOT IN (SELECT userid FROM <table2>)