1

I want to combine the results of multiple queries into one result. All queries return two fields, one of them being common, the name field. I want to merge the results using the name field.

Example:

First query result:

Value  Name  
-----  ------  
30     John 
40     Nick  
50     Liam

Second query result:

Value2  Name  
-----  ------  
30     John 
40     Nick  
50     Joe

Third query result:

Value3  Name  
-----  ------  
  30     John 
  40     Tony  
  50     Jack

I want the result to look like this:

  Value  Value2 Value3 Name
  .....  ...... .....  ....
   30     30      30    John
   40     40            Nick
   50                   Liam
          50            Joe
                  40    Tony
                  50    Jack
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
robert.leuca
  • 53
  • 5

1 Answers1

3

You can use union all and aggregation:

select name,
       max(value1) as value1,
       max(value2) as value2,
       max(value3) as value3
from ((select name, value as value1, null as value2, null as value3
       from q1
      ) union all
      (select name, null as value1, value as value2, null as value3
       from q2
      ) union all
      (select name, null as value1, null as value2, value as value3
       from q3
      )
     ) q
group by name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What do the `q`s do? Are they an arbitrary representation of that self-contained query, or are they defined somewhere else? For instance, if my table was `C7` would I use that for all the `from`s or just the last one? – Travis Heeter Oct 16 '17 at 16:27
  • @TravisHeeter . . . "First query result", "Second query result", "Third query result". – Gordon Linoff Oct 17 '17 at 01:02