Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ...
share|improve this question
2  
You want 'UNION' not 'UNION ALL'. –  Mark Storey-Smith Jan 23 '12 at 10:55
    
@Mark Storey-Smith: That fixed it but i can not query the name field its giving me: Error: #1054 - Unknown column 'name' in 'where clause' –  YumYumYum Jan 23 '12 at 11:01

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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 .....
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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