Table1

id	hour	date	tableValue1	tableValue2
1	3	2020-05-29	123	145
2	2	2020-05-29	1500	3400

Table2:

id	hour	date	tableValue3	tableValue4
1	1	2020-05-29	4545	3697
2	3	2020-05-29	5698	2896

Table3:

id	hour	date	tableValue5	tableValue6
1	2	2020-05-29	7841	5879
2	1	2020-05-29	1485	3987

I want to select multiple columns from different tables with one query.

Expected Output:

hour	tableValue1	tableValue3	tableValue5
1	0	4545	1485
2	1500	0	7841
3	123	5698	0

I've tried this query without success:

SELECT hour , tableValue1 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10 FROM table1 
UNION ALL
SELECT hour , tableValue3 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10 FROM table2 
UNION ALL
SELECT hour , tableValue5 WHERE date = "2020-05-29" AND hour BETWEEN 10 AND 10 FROM table3 

I'm getting instead the following:

hour	tableValue1
3	123
2	1500
1	4545
3	5698
2	5879
1	3987

The columns tables have in common are hour and date, do I need to redesign database structure to link the tables, so that I can use JOIN command, but how? Or is there a sql command to select multiple column from multiple tables?

CodePudding user response：

You must introduce empty columns in first query

SELECT hour , tableValue1, 0 tableValue3, 0 tableValue5 FROM table1 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10  
UNION ALL
SELECT hour , 0, tableValue3, 0  FROM table2 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10
UNION ALL
SELECT hour , 0,0 tableValue5 FROM table3 WHERE date = "2020-05-29" AND hour BETWEEN 10 AND 10 

CodePudding user response：

There are a couple of issues in your code:

• your WHERE clause should be found after the FROM clause in your subqueries
• you want different columns, but you associate only one column for each of your table: if you want three columns, each of your subqueries should return three columns
• your rows are not ordered because you're missing an ORDER BY clause at the end of your code.

The fixes that would allow you to get to the expected output are contained in the following code:

SELECT hour, 
       tableValue1, 
       0 AS tableValue3, 
       0 AS tableValue5 
FROM table1 
WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10

UNION ALL

SELECT hour, 
       0 AS tableValue1, 
       tableValue3, 
       0 AS tableValue5 
FROM table2
WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10

UNION ALL

SELECT hour, 
       0 AS tableValue1, 
       0 AS tableValue3, 
       tableValue5 
FROM table3
WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10

ORDER BY hour

Check the demo here.