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.