title: Conditional JOIN based on column value
body: <p>I've looked all over, and unfortunately, I can't seem to figure out what I'm doing wrong. I'm developing a personal financial management application that uses a MySQL server. For this problem, I have 4 tables I'm working with.</p>
  
  <p>The <code>TRANSACTIONS</code> table contains columns <code>CATID</code> and <code>BILLID</code> which refer to primary keys in the <code>SECONDARYCATEGORIES</code> and <code>BILLS</code> tables. Both the <code>TRANSACTIONS</code> and <code>BILLS</code> tables have a column <code>PCATID</code> which refers to a primary key in the <code>PRIMARYCATEGORIES</code> table.</p>
  
  <p>I'm building a SQL query that sums an "amount" column in the <code>TRANSACTIONS</code> table and returns the primary key from <code>PCATID</code> and the sum from all records that are associated with that value. If the <code>BILLID</code> is set to <code>-1</code>, it should find the <code>PCATID</code> in <code>SECONDARYCATEGORIES</code> where <code>SECONDARYCATEGORIES.ID = TRANSACTIONS.CATID</code>, otherwise (since <code>-1</code> indicates this is NOT a bill), it should find the <code>PCATID</code> from the <code>BILL</code> record where <code>BILLS.ID</code> matches <code>TRANSACTIONS.BILLID</code>.</p>
  
  <p>I'm looking for something like this (not valid SQL, obviously):</p>
  
  <pre><code>SELECT
   SECONDARYCATEGORIES.PCATID,
   SUM(TRANSACTIONS.AMOUNT)
  FROM
   TRANSACTIONS
  IF (BILLID = -1) JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = TRANSACTIONS.CATID
  ELSE JOIN SECONDARYCATEGORIES ON SECONDARYCATEGORIES.ID = BILLS.CATID WHERE BILLS.ID = TRANSACTIONS.BILLID
  </code></pre>
  
  <p>I have tried a myriad of different JOINs, IF statements, etc, and I just can't seem to make this work. I had thought of breaking this up into different SQL queries based on the value of <code>BILLID</code>, and summing the values, but I'd really like to do this all in one SQL query if possible.</p>
  
  <p>I know I'm missing something obvious here; any help is very much appreciated.</p>
  
  <p>Edit: I forgot to describe the BILLS table. It contains a primary category, ID, as well as some descriptive data.</p>