How to join two table and show one query result in MySQL?

Question

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there is duplicate. So my expected output records will contain 5 rows not 6 rows. How do i do that?

Example:

table1: table2:
+-------------------------+ +--------------------------------+
| Name | Name
--------------------------- +---------------------------------
| A | | C |
| B | | D |
| C | | E |
My Expected output is:
+-------------------------+
| Name | ID 
---------------------------
| A | 1 table1
| B | 2 table1
| C | 3 table2 or table1 (no unique)
| D | 4 table2
| E | 5 table2

I tried this:

 SELECT 
 name as name 
 FROM 
 table1
UNION
 SELECT 
 anothernamename as name 
 FROM 
 table2
WHERE
 name like '%C%'

Error: #1054 - Unknown column 'name' in 'where clause'

Follow up: Union with Where clause + extra extra large requirement.

SELECT * FROM 
(
 ( 
 SELECT * FROM table1 
 WHERE ...
 ORDER BY ...
 LIMIT ...
 ) 
UNION 
 ( 
 SELECT * FROM table2 
 WHERE ...
 ORDER BY ...
 LIMIT ...
 )
) as t
WHERE ...
 ORDER BY ...

Answer 1

Here is the query you need:

SELECT name FROM
(
 SELECT name FROM table1
 UNION
 SELECT name FROM table2
) A;

Here is some sample code based on your question:

use test
drop table if exists table1;
drop table if exists table2;
create table table1
(
 id int not null auto_increment,
 name varchar(10),
 primary key (id)
);
create table table2 like table1;
insert into table1 (name) values ('A'),('B'),('C');
insert into table2 (name) values ('C'),('D'),('E');
SELECT name FROM
(
 SELECT name FROM table1
 UNION
 SELECT name FROM table2
) A;

Here is the execution of that sample code:

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.03 sec)
mysql> drop table if exists table2;
Query OK, 0 rows affected (0.03 sec)
mysql> create table table1 (
 -> id int not null auto_increment,
 -> name varchar(10),
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.05 sec)
mysql> create table table2 like table1;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into table1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into table2 (name) values ('C'),('D'),('E');
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT name FROM (SELECT name FROM table1
 -> UNION SELECT name FROM table2) A;
+------+
| name |
+------+
| A |
| B |
| C |
| D |
| E |
+------+
5 rows in set (0.00 sec)
mysql>

Give it a Try !!!

• Krishna: In the output the row will be repeated for C both from table1 and table2.

Answer 2

It seems that your error it's cause en table2 the column you want to filter is anothernamename, not name, you can't put an alias on the WHERE. In any case, in your question, you don't seem to want to filter your results, so that WHERE is of no use at all. Your query should just be:

SELECT 
FROM ( SELECT [name]
 FROM table1
 UNION
 SELECT anothernamename 
 FROM table2) AS Data
WHERE ....
ORDER BY ....
LIMIT .....