title: Adding an additional column to SQL UNION SELECT
body: <p>Suppose I have the following mapped and normalized tables;</p>
  
  <pre><code>Group                       User                        Contact                     BelongsTo       
  +-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
  |    gID|  name|  col3|     |    uID| fname| sname|     |    cID|  name|  col3|     |  accID|   gID|
  +-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
  |1      |ABC   |?     |     |1      |JJ    |BB    |     |4      |ABCD  |?     |     |1      |2     |
  +-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
  |2      |BCD   |?     |     |2      |CC    |LL    |     |5      |BCDE  |?     |     |3      |2     |
  +-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
  |3      |DEF   |?     |     |3      |RR    |NN    |     |6      |CDEF  |?     |     |5      |3     |
  +-------+------+------+     +-------+------+------+     +-------+------+------+     +-------+------+
  </code></pre>
  
  <p>Using EERM, User and Contact are subclasses of "Account" superclass. (not shown) An account can belong to many groups, thus "BelongsTo" table records the M:N relationship between the Accounts and Group membership.</p>
  
  <p>I would like an SQL statement which will allow me to query all the users and contacts that have membership in a Group matching conditions as follows:</p>
  
  <pre><code>SELECT
      tc."cID" AS "accID",
      tc."name" AS "accName",
      tbt."gID"
  FROM "tblContact" tc
      INNER JOIN "tblBelongsTo" tbt
          ON tbt."accID" = tc."cID"
  UNION SELECT
      tu."uID" AS "accID",
      CONCAT (tu."fname", ' ', tu."sname") AS "accName",
      tbt."gID"
  FROM "tblUser" tu
      INNER JOIN "tblBelongsTo" tbt
          ON tbt."accID" = tu."uID"
  ORDER BY "accID" ASC;
  </code></pre>
  
  <p>The above works, I have combined UNION SELECT in the query as the number of columns match either side when I CONCAT the forename and surname together. Resulting in a global "account_name" &amp; "account_id" column.</p>
  
  <p>My question is this: How would I go about adding an extra column so that I can see what the group name is?</p>
  
  <p>ie from this:</p>
  
  <pre><code>Result
  +-------+-------+------+
  |  accID|accName|   gID|
  +-------+-------+------+
  |1      |JJBB   |2     |
  +-------+-------+------+
  |3      |RRNN   |2     |
  +-------+-------+------+
  |5      |BCDE   |3     |
  +-------+-------+------+
  </code></pre>
  
  <p>to this:</p>
  
  <pre><code>Result (2)
  +-------+-------+------+------+
  |  accID|accName|   gID|  name|
  +-------+-------+------+------+
  |1      |JJBB   |2     |   BCD|
  +-------+-------+------+------+
  |3      |RRNN   |2     |   BCD|
  +-------+-------+------+------+
  |5      |BCDE   |3     |   DEF|
  +-------+-------+------+------+
  </code></pre>
  
  <p>It seems everything I have tried causes the UNION SELECT to break (because of unmatched column). Likewise, I had little luck in combining sub-queries. I am probably missing something very obvious...</p>
  
  <p>Thanks in advance.</p>