title: MySQL: Select one row from left join only once in result wth multiple rows
body: <p>Alright, with help from SO and some of my own admittedly limited brain power, I got my query mostly working. I'm getting the result I want from the <code>LEFT JOIN</code> and it's showing up in the outer query. However, it shows up multiple times for each row with the same ID, which is expected, but that's not what I need...</p>
  
  <p><strong>I am wondering if it's possible to only show the joined result once.</strong> I found lots of answers online for only pulling the first row from a table that was left joined. However, what I'm looking for is to only show the joined result once <em>in the result</em> (doesn't have to be the first time).</p>
  
  <p>This is [a simplified version of] my query:</p>
  
  <pre><code>select
      `order`.order_id,
      `order`.total,
      `order`.comment,
      `order`.shipping_cost,
      `ct`.amount as credit
  from `order`
  left join (
      select
          customer_id,
          sum(amount) as amount
      from `customer_transaction`
      where
          amount &gt; 0
          and integrated = 0
      group by
          customer_id
  ) as ct
      on `ct`.`customer_id` = `order`.`customer_id`
  where
      `order`.integrated = 0;
  </code></pre>
  
  <p>What this is supposed to do is get not-yet-integrated order information while including customer credit. Unfortunately the credit is stored as rows in the <code>ct</code> table, not associated with a specific order, which sort of makes sense, but the software that is integrating this wants this number on an order.</p>
  
  <p>The above query "works", until one customer has more than one order. In this case, if I use the above query, it will effectively think all orders had credit applied. For example, the above query returns a result like:</p>
  
  <pre><code>+-------------+----------+-----------+---------+---------------+--------+
  | customer_id | order_id | total     | comment | shipping_cost | credit |
  +-------------+----------+-----------+---------+---------------+--------+
  |       52227 |   184589 |   28.6910 |         |       12.7410 | 8.0000 |
  |       52227 |   184590 |   28.6910 |         |       12.7410 | 8.0000 |
  +-------------+----------+-----------+---------+---------------+--------+
  </code></pre>
  
  <p>This customer has $8 in credit "on their account", not $16. I.e., the sum of non-used credit transactions on the customer adds up to 8. I am trying to get that credit to only show up once. In other words, this is the result I would like:</p>
  
  <pre><code>+-------------+----------+-----------+---------+---------------+--------+
  | customer_id | order_id | total     | comment | shipping_cost | credit |
  +-------------+----------+-----------+---------+---------------+--------+
  |       52227 |   184589 |   28.6910 |         |       12.7410 | 8.0000 |
  |       52227 |   184590 |   28.6910 |         |       12.7410 | NULL   |
  +-------------+----------+-----------+---------+---------------+--------+
  </code></pre>
  
  <p>(Note the NULL in the bottom right corner. It could also be 0, or maybe even 8/$NUM_ROWS. I'd have to check if the software would work with that. Probably not.)</p>
  
  <p>The reason for this craziness is because one piece of software stores the credit as one or more rows associated with the customer, and the other expects it as part of one order, and it's supposed to all integrate "at once". It runs one query to get all order info, including credit, and a second for line-items (no credit). So...I need to bring the credit in with the order, then set it to integrated=1 so it won't be used the next time around (I already have this part working).</p>
  
  <p>Any ideas how I can only display the credit once, or is this not doable with a "simple" query (as opposed to code)?</p>
  
  <p>Thanks</p>