Intersection Joins

[Zeest]

I have this situation where I want to replace a delete query in a loop with one mass query. The problem that I face, I will try to outline here.

There are 3 tables

a simple mock up....
[code]
table_1 table_2 table_3

id id id
1 1 2
2 2 3
3 5 4
5 6 5
7 8 6
9 7
8
9
10
[/code]


Now here's what I want to do...

Join tables table_1 and table_2 first, using id so that I get an intersection of ids in the tables

again an example

[code]
result of join 1

table_1 table_2

id other_table_1_data... id other_table_2_data...
1 1
2 2
3 NULL
5 5
NULL 6
7 NULL
NULL 8
9 NULL
[/code]


As you can see a simple LEFT/RIGHT join wouldn't help since I'll only get all rows in one table for that but what I want is an intersection of rows in tables 1 and 2.

The next part is relatively simple and I guess I have that figured out, I need to compare this result table with table_3 to see which records in the result Set are not present in table_3 so a simple LEFT join outta do it.

Then I need to delete rows from table_1 and table_2 that are not present in table_3. I can do this separately using table 1 and 3 on left join and then table 2 and 3, but doing them in one query could definitely save resources since it runs every 15 minutes on my already overloaded server.

[effigy]

I haven't upgraded my MySQL at home, so I cannot test this out--would a union work?

[code] (select t1.id, t2.id from table_1 t1 left join table_2 t2 on t1.id = t2.id)
union
(select t1.id, t2.id from table_1 t1 right join table_2 t2 on t1.id = t2.id)[/code]

[Zeest]

Thanks that code outputs exactly the table I've shown in step 2.

The problem now though is that I don't know how I'm supposed to LEFT JOIN table_3 to the results table achieved by this query since it has union and all and I'm not that familiar with this syntax.

The output should be somewhat like this

[code]
table_1 table_2 table_3
1 1 NULL
2 2 2
3 NULL 3
5 5 5
NULL 6 6
7 NULL 7
NULL 8 8
9 NULL 9
[/code]

what I want basically is

(those rows present in table1 union table2) but not in (table3)

so once I get the above result table all I have to do is add a WHERE table_3.id IS NULL

in this case that row is 1

This row is to be deleted from table_1 and table_2 in one query.

another example

table1: 3, 6, 9, 10
table2: 1, 3, 5, 7
table3: 8, 10

from table1 3,6,9 should be deleted
from table2 1,3,5,7 should be deleted

I'm referring to cross table delete http://www.electrictoolbox.com/article/mysql/cross-table-delete/

[effigy]

You'll need a temporary table. Try something like this...

[code]create temporary table table_name (select ...) union (select ...);[/code]

...then use the temporary table in a select statement as you would any other and join it to table 3.

[Zeest]

Whats the life time of a temporary table? I assume that if I create a temp table in one mysql_query, then I have to do another query to use that table, isn't it?

so something like
[code]
mysql_query("
create temporary table abc
{
(select t1.id, t2.id from table_1 t1 left join table_2 t2 on t1.id = t2.id)
union
(select t1.id, t2.id from table_1 t1 right join table_2 t2 on t1.id = t2.id)
}
");

mysql_query("
DELETE table_1.*, table_2.*
FROM abc
LEFT JOIN table_3
      ON abc.id=table_3.id
WHERE
      table_3.id IS NULL
")
[/code]

should work.....?

[effigy]

From the [url=http://dev.mysql.com/doc/refman/5.0/en/create-table.html]MySQL 5.0 Manual[/url]:

[quote]You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.[/quote]