title: Combining multiple SELECT Queries from multiple views to count data
body: <p>So I'm trying to build what amounts to a report from several MySQL views that I have already written.  Each one of the views has three columns and looks something like this</p>
  
  <pre><code>View ABC:
  -------------------------------
  |    A    |    B    |    C    |
  |         |         |         |
  -------------------------------
  |  data1  |   asdf  |    3    |
  |  data1  |   jkl;  |    1    |
  -------------------------------
  </code></pre>
  
  <p>Assume that there are three other views which are structured identically, named DEF, GHI, and JKL.  In view DEF, the last column is named D, in GHI the last column is named E, and in JKL the last column is named F.  </p>
  
  <p>In each of the views, the value in the A column should be the same for all rows (It's for another way to solve the problem I'm having which doesn't completely rely on MySQL, but I'm sure there must be a better way).  </p>
  
  <p>What I'm trying to do is this:  </p>
  
  <pre><code>View ABC:
  -------------------------------------------------------------
  |    A    |    B    |    C    |    D    |    E    |    F    |
  |         |         |         |         |         |         |
  -------------------------------------------------------------
  |  data1  |   asdf  |    3    |    0    |    2    |    7    |
  |  data1  |   jkl;  |    1    |    1    |    0    |    0    |
  |  data2  |   lmno  |    0    |    1    |    2    |    17   |
  -------------------------------------------------------------
  </code></pre>
  
  <p>So my first thought would be to LEFT OUTER JOIN on the B field, like so:  </p>
  
  <pre><code>SELECT ABC.*, DEF.D, GHI.E, JKL.F 
  FROM ABC 
  LEFT OUTER JOIN DEF ON ABC.B = DEF.B 
  LEFT OUTER JOIN GHI ON ABC.B = GHI.B
  LEFT OUTER JOIN JKL ON ABC.B = JKL.B
  </code></pre>
  
  <p>That's all well and good; it fills in zeroes with NULL, which I can easily COALESCE into 0.  However, the issue is if there is a row in one of the DEF, GHI, or JKL views where the username doesn't match one in ABC; that row is simply omitted from my SELECT (due to this being a LEFT OUTER JOIN).  RIGHT OUTER JOIN suffers the same issue.  </p>
  
  <p>Is there a way to perform a full outer join such that any B that occurs in any of the four views will have a row in my SELECT?  I've tried embedding some other selects, but unfortunately that seems to repeat rows and data values in an unacceptable way in the result of the SELECT.  From looking at the documentation, I don't think MySQL supports a full outer join (at least not that I could find), and even if it did, I don't know how I could get the username from the other views into the username column in the SELECT statement.  </p>