Creating Multiple Tables with WITH statements

Question

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.

Answer 1

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

Answer 2

You try to use table1 and table2 before their creation... reorder tables in WITH clause.

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

SELECT DISTINCT * 
FROM table3 
ORDER BY c3;