1

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.

2
  • why do you need cte's here? Can you explain the problem you are trying to solve?
    – S3S
    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.
    – Abdul
    Nov 7, 2018 at 20:11

2 Answers 2

0
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
2
  • 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?
    – Abdul
    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!
    – Abdul
    Nov 7, 2018 at 23:32
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.