Welcome to WebmasterWorld Guest from 54.196.243.192

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Cross table MySQL Query help

     
7:14 pm on Nov 7, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 2, 2003
posts:95
votes: 0


Hi,

I have a problem were I have 4 identically structured tables containing member data and I need unique rows across each.

For an individual table I would perform:
SELECT id, COUNT(id) AS count FROM members_9_2004 GROUP BY username, nickname HAVING count > 1

Then I would delete the data that occurs more than once.

Now a problem has erose were the same data may occur across tables.

Can anyone help?

12:35 am on Nov 8, 2004 (gmt 0)

Full Member

10+ Year Member

joined:Oct 9, 2003
posts:314
votes: 0


Since your tables all have identical structures, if they are MYISAM type tables you could use a MERGE type table. To do this, create yet another table also with the same structure, but make its type MERGE instead MYISAM. eg:

CREATE TABLE new_table_name (
...
same data structure
...
) TYPE = MERGE UNION = (table1, table2, table3, table4)
INSERT_METHOD = [NO, FIRST, or LAST]; # ***

***optional declaration to forbid INSERTs or to determine which table to make INSERTs into

A SELECT query to this table will query all the constituent tables in the UNION.

MERGE type tables also support DELETE, UPDATE and INSERT. When you find duplicates, a DELETE query to the MERGE table would DELETE from all the tables, then you could reINSERT into one of them.

I hope this helps

11:25 am on Nov 8, 2004 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 2, 2003
posts:95
votes: 0


Haha sure does help! Thats awesome, I was scared it would be slow cause I had to do it on the production machine... but it was lightening fast.

Muchos gracias!

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members