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 !!!
There are many ways to do this. Most common are with a LEFT JOIN
/ IS NULL
check, NOT IN
or NOT EXISTS
subquery. Here's a solution with the 3rd option:
SELECT t2.id, t2.name
FROM table2 AS t2
WHERE NOT EXISTS
( SELECT *
FROM table3 AS t3
WHERE t3.table1_id = @t1_id -- the t1.id value (choice) that you want to check
AND t3.table2_id = t2.id
) ;
Best Answer
You can try this:
And then delete from Table2 using newly created table Table3