subqueries , outer joints, temporary tables?

[kharlo]

hi , my problem is that i want to make a query that gives me the rows in table A which have no rows related in table B.

i have a USER table which has cod_us field , and a CERT table which has cod_us_cert field related with the field of the former.

And this query will grow bacause i have to do that same thing with 2 or more tables related to the table USER in the same query.

i wonder if you know what is the best thing i can do , outer joints or temp tables , im using mysql 4.0.15 and i know it does not support subqueries

[Drakla]

Sounds like the ubiquitous left join is what you need

SELECT id from table1 LEFT JOIN table2
ON table1.cod_us=table2.cod_us_cert WHERE table2.cod_us_cert IS NULL

Then you could read all the rows into an array, implode them and do whatever you want with them, like

while ($row=mysql_fetch_row($res))
{
    $ids[]=$row[0];
}

$ids=implode(',', $ids);

$query="DELETE FROM table1 WHERE id in ($ids)";

If you did want a temporary table using the create -> select thing is really good for it, like:

CREATE TEMPORARY TABLE tempTab AS SELECT id from table1 LEFT JOIN table2
ON table1.cod_us=table2.cod_us_cert WHERE table2.cod_us_cert IS NULL

And in case you haven't used them much, you don't have to worry about temp tables interfering with each other. If you've got 100 scrips running creating the same named temp table they're 'just pretend' so you won't write into ones present in the other scripts.

Hope that does for you.

[kharlo]

thanks , i've already use temp tables , but im worried about the resources because although the tables will be deleted when the thread connected to the database got killed i know the time to build them will take a little more time that i expected.

by the way , i used left joints too, thanks