title: How can I JOIN two tables, but only include each row from the right table once?
body: <p>I have a database with some tables, which I want to join, but though there will be multiple matches, want the budgets table rows to only appear next to the first row with which they match, to allow me to sum the budgets after the group by without duplicating the same budget.</p>
  
  <pre><code>SELECT GROUP_CONCAT(DISTINCT categories.categoryId), 
  GROUP_CONCAT(budgets.budget) FROM transactions
      JOIN categories ON transactions.categoryId = categories.categoryId
      JOIN budgets ON transactions.categoryId = budgets.categoryId
      WHERE transactions.userId = 1 AND transactions.date BETWEEN DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND CURDATE()
  GROUP BY categories.displayName
  </code></pre>
  
  <p>Essentially, I want the GROUP_CONCAT(budgets.budget) column to return 204, 209, 206; and not 204, 204, 209, 206</p>
  
  <p>I'm using this as a workaround using 2 queries if that is any help:</p>
  
  <pre><code>SELECT SUM(transactions.amount) AS spend, categories.displayName, 
  GROUP_CONCAT(DISTINCT categories.categoryId) AS id FROM transactions
  JOIN categories ON transactions.categoryId = categories.categoryId
  WHERE transactions.userId = ${req.params.id} AND transactions.date BETWEEN DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND CURDATE()
  GROUP BY categories.displayName
  
  SELECT SUM(budgets.budget) AS budget, categories.displayName, GROUP_CONCAT(DISTINCT categories.categoryId) FROM budgets 
  JOIN categories ON categories.categoryId = budgets.categoryId
  WHERE budgets.userId = ${req.params.id}
  GROUP BY categories.displayName
  
  
  res1.forEach((group, key) =&gt; {
    res2.forEach((result) =&gt; {
      if (result.displayName === group.displayName) {
        res1[key].budget = result.budget;
      }
    });
  });
  </code></pre>