homepage Welcome to WebmasterWorld Guest from 184.73.40.21
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Cross table MySQL Query help
XMLMania

10+ Year Member



 
Msg#: 5623 posted 7:14 pm on Nov 7, 2004 (gmt 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?

 

Salsa

10+ Year Member



 
Msg#: 5623 posted 12:35 am on Nov 8, 2004 (gmt 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

XMLMania

10+ Year Member



 
Msg#: 5623 posted 11:25 am on Nov 8, 2004 (gmt 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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved