By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

mySQL get information from multiple tables with efficient query

P: 3
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):
Expand|Select|Wrap|Line Numbers
  1.  
  2. ID  sendingid         list        fromaccount    
  3. 1        a1        user_new1        noreply@example.com
  4. 2         a2        user_new1        noreply@example.com
  5. 3         a3        user_new2        noreply@example.com
  6. 4         a4        user_new4        noreply@example.com
  7.  
  8.  
Table 2(Tracking - This is the main task tracking table,which track which user opened our activity):
Expand|Select|Wrap|Line Numbers
  1.  
  2. ID  trackingid         emails            
  3. 1        a1        myexampla@gmail.com
  4. 2         a2        example@hotmail.com 
  5. 3         a3        newexample@yahoo.com
  6. 4         a4        simpler@gmail.com
  7.  
Table 3(user_new1- This is the list which is attached with activity send under the column name list):
Expand|Select|Wrap|Line Numbers
  1.  
  2. ID  fname         emails                    price
  3. 1        John        myexampla@gmail.com      10
  4. 2         Bill        example@hotmail.com      20
  5. 3         sami        newexample@yahoo.com      20
  6. 4         daweoo        simpler@gmail.com        20
  7.  
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
Jun 26 '12 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,347
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.
Jun 26 '12 #2

P: 3
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
Jun 26 '12 #3

Rabbit
Expert Mod 10K+
P: 12,347
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.
Jun 26 '12 #4

P: 3
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
Jun 26 '12 #5

Rabbit
Expert Mod 10K+
P: 12,347
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.
Jun 26 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.