mySQL get information from multiple tables with efficient query

[mehrtahir]

I'm new to MySQL, and cannot seem to get the desired information out, after trying a few different google searches, and reading through some MySQL tutorials.


Given the following sample tables:

Table 1(Activity This is activity table which is send to the clients):

 
ID  sendingid         list        fromaccount    
1        a1        user_new1        [e-mail]
2         a2        user_new1        [e-mail]
3         a3        user_new2        [e-mail]
4         a4        user_new4        [e-mail]
 
 

Table 2(Tracking - This is the main task tracking table,which track which user opened our activity):

 
ID  trackingid         emails            
1        a1        [e-mail]
2         a2        [e-mail] 
3         a3        [e-mail]
4         a4        [e-mail]
 

Table 3(user_new1- This is the list which is attached with activity send under the column name list):

 
ID  fname         emails                    price
1        John        [e-mail]      10
2         Bill        [e-mail]      20
3         sami        [e-mail]      20
4         daweoo        [e-mail]        20
 

The user_new1,user_new2,user_new3 .......user_new80 tables. All tables have similar structure.

first i will find unique email address from tracking table then i find to which list of the activity table it is send,
then i will delete that record from list say user_new1 and move it to another temprary table

[Rabbit]

You should not have multiple tables for user_new. It breaks normalization rules and makes querying difficult, as you've already seen. It should be just one table.

[mehrtahir]

yes infact it will make difficult,but i need solution for it.
is it possible in two queries if yes please tell me the method how to write then that.
but you must remmeber i have millions of records in my database

[Rabbit]

My suggestion is that you fix your data.

But if you have to work around it, use a union query to simulate the correct data structure, or build a dynamic string and execute that.

[mehrtahir]

can you show me some code how can i do this,
also as i mention we have hug data,join query will run slow in this case or not

[Rabbit]

A join query doesn't have to be slow. It's all based on how well the data is designed. And currently your data is badly designed. So yes, it may well be slow, but that has nothing to do with using a join or a union.

Sorry, I don't give out code unless the poster has shown an attempt.