title: Difficulty with SQL UNION involving queries with different number of columns, and other woes
body: <p>I'm part of a Toastmasters club, and just got put in charge of scheduling which members serve which roles in the weekly meetings.  I'm writing a small app to help manage this, with the idea that a database can help me determine fairly which member is most overdue to serve in each role.</p>
  
  <p>To simplify matters, let's say that my database has two tables... <code>MEMBER</code> and <code>ROLE_PERFORMED</code>:</p>
  
  <pre><code>MEMBER
  ------
  ID int
  NAME varchar
  
  
  ROLE_PERFORMED
  --------------
  MEMBER_ID int   (half of primary key, and also foreign key)
  DATE date   (other half of primary key)
  ROLE int   (0-11, mapped to an enumeration at the application layer)
  </code></pre>
  
  <p>My goal is to write a SQL query for each role type, which will give me:</p>
  
  <ul>
  <li><p><code>MEMBER.NAME</code>, for all rows in the <code>MEMBER</code> table</p></li>
  <li><p>A second column, containing the <em>most recent</em> <code>ROLE_PERFORMED.DATE</code> value matching that member and role... or else a <code>NULL</code> (or some other placeholder) if the member has <em>never</em> served in that role</p></li>
  </ul>
  
  <p>I could then order by the date/placeholder column, and assign roles in order of who has gone the longest time without performing that role.  Something like this:</p>
  
  <pre><code>ROLE     NAME    MOST_RECENTLY_PERFORMED
  --------------------------------
  1        John    &lt;null&gt;
  1        Joe     2010-02-25
  1        Bob     2010-09-14
  </code></pre>
  
  <p>The approach I'm currently taking is to try a <code>UNION</code>... between one <code>SELECT</code> that grabs those members who have performed the role, and a second SELECT grabbing members who have never performed the role.  Something like this:</p>
  
  <pre><code>SELECT m.name, r.date FROM member m, role_performed r WHERE m.id = r.member_id and r.role = 1
  UNION
  SELECT m.name, (NULL?????) FROM member m, role_performed r WHERE (?????)
  </code></pre>
  
  <p>There are three problems with this, however, for which I'm hoping someone may have solutions:</p>
  
  <ul>
  <li><p>The first <code>SELECT</code> grabs <em>all</em> dates for which the member performed the role, not just the most recent.</p></li>
  <li><p>The second <code>SELECT</code> causes the <code>UNION</code> to fail, because I can't find an acceptable placeholder value to use for the second date column... and <code>UNION</code>'s require each query to return the same number of columns.</p></li>
  <li><p>The second <code>WHERE</code> clause is simply beyond my limited SQL skills.  How do you find all <code>MEMBER</code> rows, which do <em>not</em> have a <code>ROLE_PERFORMED</code> rows matching that member and that role?  Bear in mind that there <em>can</em> still be <code>ROLE_PERFORMED</code> rows matching that member and some <em>other</em> role.</p></li>
  </ul>
  
  <p>Like I said, I'm not the greatest SQL guru in the world... so I would welcome altogether different approaches if I'm making this more complicated than it needs to be.  Heck, I would welcome a better question title to help meaningfully present what I'm asking!  Thanks a lot in advance.</p>