mysql troubles with left joins

[AN-net]

ok im trying to figure out a way to compare a table to another to find rows that no longer have a parent. similar to orphan thread but my only problem is that the column names are different but hold the same data. so the USE() function isnt possible and i do not think ON() will work because it will only find the rows that do coinside with table 1 from table 2. basically i want find journals that no longer have journalists and remove them.

[sabret00the]

what i'd personally do is run a high tensity script doing all the work in php.


$users = U_QUERY;
while ($all_users = $db->fetch_array(U_QUERY))
{
$current_users[] = $all_users['userid'];
}

$journals = J_QUERY;

while ($all_journals = $db->fetch_array(J_USERS))
{
if (!in_array($all_journals['userid'], $current_users))
{
$journals_to_be_deleted = $all_journals['userid'] . ", ";
}
}

// do delete query here


something like that :)

[AN-net]

thanks for the suggestion but not what i was looking for ;)

[sabret00the]

thanks for the suggestion but not what i was looking for ;)

i know but atleast it's something ;)

[Paul M]

SELECT * from table1
LEFT JOIN table2 ON (table1.column1 = table2.column2)
WHERE table2.column2 = NULL

Should find any entries in table1 that don't have a matching entry in table2 (column1 & column2 are the same data you referred to with different names in each table).