Using union multiple times with distinct on top of the final results

Question

I cannot figure out how to merge 4 different columns from 2 different tables together into just one single column of all possible results, with duplicates removed. The actual names are different, but suppose I have the following two tables

Table1

1. Field1
2. Field2
3. SomethingElse

Table2

1. Field1
2. Field2
3. SomethingElse

Now in the end, I would like to merge all 4 of these fields together into one large field, and then use distinct on top of that to eliminate any duplicates, as there are many of them. The final result should be just one single column containing every possible value found in all 4 columns, without any duplicated.

When I was working with just one single table with two fields, I had it working fine:

select distinct(Field1) from Table1 where SomethingElse = SomeVal
union
(select distinct(Field2) from Table1 where SomethingElse = SomeVal)
order by 1

(of course I wished to have run distinct around the final result rather than each field)

Now I needed to add 2 more fields from another table. Nothing I have tried has even run, can't get the syntax right.

Solution 1

Here is one way:

select distinct val
from ((select field1 as val from table1 where somethingelse = someval) union all
      (select field2 from table1 where somethingelse = someval) union all
      (select field1 from table2 where somethingelse = someval) union all
      (select field2 from table2 where somethingelse = someval)
     ) t

I combine the subqueries using union all and then only do the distinct once on the outer level.

Since the next thing I would want to know is where these values are being used, here is the query for that:

select val, SUM(t1f1), SUM(t1f2), SUM(t2f1), SUM(t2f2)
from ((select field1 as val, 1 as t1f1, 0 as t1f2, 0 as t2f1, 0 as t2f2 from table1 where somethingelse = someval) union all
      (select field2, 0 as t1f1, 1 as t1f2, 0 as t2f1, 0 as t2f2 from table1 where somethingelse = someval) union all
      (select field1, 0 as t1f1, 0 as t1f2, 1 as t2f1, 0 as t2f2 from table2 where somethingelse = someval) union all
      (select field2, 0 as t1f1, 0 as t1f2, 0 as t2f1, 1 as t2f2 from table2 where somethingelse = someval)
     ) t
group by val

Solution 2

select distinct(Field1) from 
(
select Field1 [Field1] from Table1 where SomethingElse = SomeVal
union all
select Field2 [Field1] from Table1 where SomethingElse = SomeVal
) rawResults
order by 1

Something like that should work. Just union together your selects and put that in an in-line view and put your distinct and order by on the outside. I used union all since you are going to do a distinct once on the outside anyway