将不同表中的不同字段存储到临时表中

Question

I am trying to create a temporary table where it has two fields (user,pass) which was a combination from three tables

TABLES

agents
username | password

clients
username | password

admin
username | password

i want their data from their fields to be stored to a temporary table

TEMPORARY TABLE

temp_tbl
username | password

This is my MySQL query (i know this is a wrong syntax)

CREATE TEMPORARY TABLE temp_tbl ENGINE=MEMORY 
AS (SELECT username,password FROM agents UNION SELECT username,password FROM clients UNION SELECT username,password FROM admin) AS col1, col2;

Answer 1

Pretty close

CREATE TEMPORARY TABLE temp_tbl ENGINE=MEMORY 
AS SELECT * FROM 
 (SELECT username,password FROM agents 
 UNION SELECT username,password FROM clients 
 UNION SELECT username,password FROM admin) AS t1;

You have to wrap those three unions inside a another SELECT and you need to give that derived table an alias. Just one name please not two (t1 instead of col1, col2)

Unless you wrap the whole thing aaround another query as I have done, you will get a syntax error.