Creating Multiple Tables with WITH statements

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.

• why do you need cte's here? Can you explain the problem you are trying to solve?
– S3S
Commented Nov 7, 2018 at 20:08
• The problem I am trying to solve is that I have one big table with 3 columns. Person1, Person2, and Location. It shows where the two have met. I am trying to create a table that show how many location has every person been to. Names can duplicate, and locations as well. X could meet Y in NYC, and X could meet Z in NYC, so both x, y, z have only been to one location. Commented Nov 7, 2018 at 20:11

``````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
``````
• Thank you very much! I tried it on my databases with minor adjustments to match it and it worked perfectly. I reviewed my expected results and it matched. The only thing not working for me is count(). I am trying to eventually count how many locations has a person visited overall. I normally use SELECT person1, count() as counter .. . . .. . and group by person1 at the end. But it wouldn't work as I am unsure where to put it here. Should I create a temporary table and make it the result of the union? Commented Nov 7, 2018 at 21:38
• Never mind, I tried to create a new table with the these contents, I was unfamiliar with what can be done with that as I always create the table through the workbench. Thanks again! Commented Nov 7, 2018 at 23:32

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;
``````