3

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 ...
0

2 Answers 2

3

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 !!!

1
  • In the output the row will be repeated for C both from table1 and table2. Mar 25, 2015 at 4:19
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 .....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.