Jump to content


Photo

Intersection Joins


  • Please log in to reply
5 replies to this topic

#1 Zeest

Zeest
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 04 July 2006 - 11:38 AM

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....
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


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

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


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.

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 04 July 2006 - 04:15 PM

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

(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)

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 Zeest

Zeest
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 04 July 2006 - 06:28 PM

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

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

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.electrict...s-table-delete/

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 04 July 2006 - 07:09 PM

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

create temporary table table_name (select ...) union (select ...);

...then use the temporary table in a select statement as you would any other and join it to table 3.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 Zeest

Zeest
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 04 July 2006 - 07:29 PM

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
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
")

should work.....?

#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 04 July 2006 - 07:44 PM

From the MySQL 5.0 Manual:

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.


Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users