Multiple SQL SELECT

Question

I've 10 tables with a lot of records. All tables have "Date" column. I want extract all data from tables for date. I can do 10 queries SELECT * FROM Table1 WHERE Date=dd/MM/yyyy, ect...but I want to do only a query with "multiple selection". How can I do this? I'm not so skilled with SQL language.

EDIT: I'm working with Microsoft Access and also MySQL (for two different desktop application, but same problem). Tables have different fields (just Date all in common), so It's not good the use of UNION.

What EXACTLY are you trying to select? We need more info. You have Table1 and Table2, ..., each with a Date column, and you want to select data from them where Date matches something? But how are these Tables related? — Derek
– Derek, Commented Aug 17, 2011 at 0:56
If you won't use UNION then put the value in a table then join your ten tables to that table. But why not use UNION? — onedaywhen
– onedaywhen, Commented Aug 17, 2011 at 7:45

jnthnjns · Accepted Answer · 2013-05-17 20:03:08Z

2

SELECT *
FROM table1,
     table2
WHERE table1.date = 'somedate'
  AND table2.date = 'somedate'

edited May 17, 2013 at 20:03

jnthnjns

8,9254 gold badges44 silver badges70 bronze badges

answered Aug 17, 2011 at 0:36

Eric

10.7k14 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Larry K Over a year ago

Umm will this work if table2 does not have any records with somedate but table1 does?

Eric Over a year ago

Then remove the 'AND table2.date = 'somedate'

Nathanial Woolls · Accepted Answer · 2011-08-17 00:34:59Z

0

Take a look at the UNION operator for including data from multiple SELECT statements.

answered Aug 17, 2011 at 0:34

Nathanial Woolls

5,30128 silver badges32 bronze badges

Comments

Jeyara · Accepted Answer · 2011-08-17 00:35:38Z

0

Your question is not so clear. Based one what i understood, you can use Union SQL statement to combine your queries and make them as a single query. But if you want to query for different dates in different tables, you can use only use multiple queries .

answered Aug 17, 2011 at 0:35

Jeyara

2,2981 gold badge24 silver badges26 bronze badges

Comments

Alex_L · Accepted Answer · 2011-08-17 00:38:32Z

0

If I right understand your question, and you want to get data from tables where all 10 tables contains the same list of fields, you can use

SELECT * FROM Table1 WHERE Date=dd/MM/yyyy
UNION ALL
SELECT * FROM Table2 WHERE Date=dd/MM/yyyy
UNION ALL    
...
UNION ALL    
SELECT * FROM Table10 WHERE Date=dd/MM/yyyy
UNION ALL

If fields are different, you need to add the fields you want to get in result set:

SELECT field1, field2 FROM Table1 WHERE Date=dd/MM/yyyy
UNION ALL
SELECT field1, field2 FROM Table2 WHERE Date=dd/MM/yyyy
UNION ALL    
...
UNION ALL    
SELECT field1, field2 FROM Table10 WHERE Date=dd/MM/yyyy
UNION ALL

answered Aug 17, 2011 at 0:38

Alex_L

2,6641 gold badge15 silver badges13 bronze badges

Comments

Larry K · Accepted Answer · 2011-08-17 00:38:37Z

0

Be careful of performance issues when using Union. -- Be sure to run some tests comparing query times of the two approaches.

Depending on your database software, you could also use a stored procedure.

And also check that the date column is indexed in each of the tables.

answered Aug 17, 2011 at 0:38

Larry K

49.4k15 gold badges92 silver badges148 bronze badges

Collectives™ on Stack Overflow

Multiple SQL SELECT

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related