Select from multi tables

Question

0.00/5 (No votes)

See more:

How do I select 1 field from multiple tables (about 50 tables) in sql statement?
I need to get a table contains, Date field then the 50's fields each field from a table.
Used this statement but it doesn't work form more than 31 tables, when put more than 31 tables error message says (Query is too complex)

SQL

select t1.day_date,t1.at_max,t2.at_max,t3.at_max,t4.at_max,t5.at_max,t6.at_max,t7.at_max,t8.at_max,t9.at_max,t10.at_max,t11.at_max,t12.at_max,t13.at_max,t14.at_max,t15.at_max,t16.at_max,t17.at_max,t18.at_max,t19.at_max,t20.at_max,t21.at_max,t22.at_max,t23.at_max,t24.at_max,t25.at_max,t26.at_max,t27.at_max,t28.at_max,t29.at_max,t30.at_max,t31.at_max
from t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31
 where
(t1.day_date = t2.day_date) and (t1.day_date = t3.day_date) and (t1.day_date = t4.day_date) and (t1.day_date = t5.day_date) and (t1.day_date = t6.day_date) and (t1.day_date = t7.day_date) and (t1.day_date = t8.day_date) and (t1.day_date = t9.day_date) and (t1.day_date = t10.day_date) and (t1.day_date = t11.day_date) and (t1.day_date = t12.day_date) and (t1.day_date = t13.day_date) and (t1.day_date = t14.day_date) and (t1.day_date = t15.day_date) and (t1.day_date = t16.day_date) and (t1.day_date = t17.day_date) and (t1.day_date = t18.day_date) and (t1.day_date = t19.day_date) and (t1.day_date = t20.day_date) and (t1.day_date = t21.day_date) and (t1.day_date = t22.day_date) and (t1.day_date = t23.day_date) and (t1.day_date = t24.day_date) and (t1.day_date = t25.day_date) and (t1.day_date = t26.day_date) and (t1.day_date = t27.day_date) and (t1.day_date = t28.day_date) and (t1.day_date = t29.day_date) and (t1.day_date = t30.day_date) and (t1.day_date = t31.day_date) and  (t1.day_date between #2014/01/01# and #2014/01/31#)

Posted 17-Feb-14 0:22am

Eman Ayad

Updated 17-Feb-14 19:56pm

v2

Add a Solution

Comments

King Fisher 17-Feb-14 6:24am

what about Joins?

Eman Ayad 18-Feb-14 1:12am

Thanks, but join doesn't take more than 31 tables,
I've edited the question.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

prakashdotnet · Answer 1 · 2014-02-17T21:26:00

Solution 3

Divide your query in multiple query and add result to temp table
1.Create 1 temp table with all column
2.Fire first query for 15 table and add result in temp table
3.Fire second query for 15 table and add result in temp table
4.retrive result for temp table

Posted 17-Feb-14 21:26pm

prakashdotnet

Comments

Eman Ayad 18-Feb-14 5:57am

Thanks, but the result should be on datagridview

prakashdotnet 18-Feb-14 6:00am

yes you can select record and from temp table to display on datagridview using ado.net

Krunal Rohit · Answer 2 · 2014-02-17T00:24:00

Solution 1

You can do that using UNION.
See,
http://stackoverflow.com/questions/17092697/select-same-column-from-multiple-tables-only-where-something-something[^]

-KR

Posted 17-Feb-14 0:24am

Krunal Rohit

Updated 17-Feb-14 0:26am

v2

Comments

Eman Ayad 18-Feb-14 1:02am

Thanks, but it will not help,
I need to get a table contains:
Date field then the 50's fields each field from a table.

Krunal Rohit 18-Feb-14 1:05am

Put some efforts then mate. Nothing is free in this world.
You have to UNION all the fields of the table.
If UNION wouldn't have been there, then this would have caused problem to us. :)

-KR

Eman Ayad 18-Feb-14 1:59am

please reread the question, I've edited it.

prakashdotnet · Answer 3 · 2014-02-17T00:25:00

Solution 2

SQL

SELECT Column FROM table1
UNION
SELECT Column FROM table2
UNION
SELECT Column FROM table3 ...

Posted 17-Feb-14 0:25am

prakashdotnet

Comments

Eman Ayad 18-Feb-14 1:03am

Thanks, but it will not help,
I need to get a table contains:
Date field then the 50's fields each field from a table.

Eman Ayad 18-Feb-14 2:00am

please reread the question, added more details.