Each SELECT statement that does not insert into a table or a variable will produce a result set.
If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.
UPDATE
Here are examples of stored procedures.
This stored procedure would return one result set:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
END;;
DELIMITER ;
This stored procedure would return two result sets:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
SELECT * FROM table_2;
END;;
DELIMITER ;
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 !!!
Best Answer
Here you can use user defined variables, as long as you only have only one table2.id
Or if you have more than one table2.id
Or you can join directly, and ave only one result set