MySQL – Creating Multiple Tables with WITH Statements

MySQLmysql-workbench

I have created Table1 and Table2 both with WITH statements. I have been trying to create Table3 with WITH statement as well that would be the UNION ALL of Table1 and Table2.

Here is my attempt: 

 WITH table3 AS 
 (
    SELECT c1 as c3 
    FROM table1 
      union all 
    SELECT c2 FROM table2
 ), table2(c2, loc2) AS 
    ( SELECT 
        character2, location FROM lotrfirstencounters
    ), table1(c1, loc1) AS 
    ( SELECT 
        character1, location FROM lotrfirstencounters
    )

SELECT DISTINCT * FROM table3 ORDER BY c3;

So when I had WITH statement right before table2 and before attempting to create table3; both table1 and table2 were created as intended and I was able to call their distinct column like this: 

  WITH table2(c2, loc2) AS 
( SELECT 
    character2, location FROM lotrfirstencounters
), table1(c1, loc1) AS 
( SELECT 
    character1, location FROM lotrfirstencounters
)

SELECT DISTINCT * FROM table1 ORDER BY c1;

I tried different ways to get a UNION ALL between them to get distinct columns for a new table but nothing I've tried seems to work.

Best Answer

CREATE TABLE #Demo
(
  Person1 VARCHAR(10),
  Person2 VARCHAR(10),
  Location VARCHAR(10)
);

INSERT #Demo SELECT 'X','Y', 'NYC';
INSERT #Demo SELECT 'X', 'Z','NYC';
INSERT #Demo SELECT 'Y', 'X','NYC';
INSERT #Demo SELECT 'Y', 'X','BOSTON';
INSERT #Demo SELECT 'Y', 'Z','WASHINGTON';

SELECT  Person1,Location from #Demo group by person1,Location 
UNION
SELECT  Person2,Location from #Demo group by person2,Location 
ORDER BY 1,2;

Drop Table #Demo;

I did a simple group by on the Person1 and Location columns but then realised that Person X could also be in the Person2 column so added a union. I'm think there is a neater solution.

The above gives the following as the output:

Person1 Location
X   BOSTON
X   NYC
Y   BOSTON
Y   NYC
Y   WASHINGTON
Z   NYC
Z   WASHINGTON
Related Question