Syntax error Best way to combine multiple advanced MySQL select queries?

Best way to combine multiple advanced MySQL select queries?

To combine multiple advanced MySQL select queries, use UNION. Let us first create a table −

mysql> create table DemoTable1 (Value1 int,Value2 int);
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(10,29);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1 values(100,190);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1 values(40,101);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+--------+--------+
| Value1 | Value2 |
+--------+--------+
| 10     | 29     |
| 100    | 190    |
| 40     | 101    |
+--------+--------+
3 rows in set (0.00 sec)

Following is the query to create second table −

mysql> create table DemoTable2 (Number1 int, Number2 int);
Query OK, 0 rows affected (0.49 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(100,290);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable2 values(200,390);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable2 values(50,170);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+---------+---------+
| Number1 | Number2 |
+---------+---------+
| 100     | 290     |
| 200     | 390     |
| 50      | 170     |
+---------+---------+
3 rows in set (0.00 sec)

Following is the query to combine multiple advanced MySQL select queries −

mysql> (select Value1,Value2 from DemoTable1)
   union
   (select Number1 AS Value1, Number2 AS Value2 from DemoTable2)
   order by Value1 DESC;

This will produce the following output −

+--------+--------+
| Value1 | Value2 |
+--------+--------+
| 200    |    390 |
| 100    |    290 |
| 100    |    190 |
| 50     |    170 |
| 40     |    101 |
| 10     |     29 |
+--------+--------+
6 rows in set (0.04 sec)
Updated on: 2019-08-22T12:13:50+05:30

420 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements