How do I join 2 tables and search for 2 conditions including a SUM <p>I have two tables, and two conditions that I am trying to write a query for.</p> <p>I need to join the two following tables:</p> <ol> <li>trans</li> <li>transunit</li> </ol> <p>They are joined by the <em>accountID</em> field which is in both tables, I also want the <em>clientID</em> which is in the trans table.</p> <p>Secondly I also need for the field <em>allocated</em> in trans table to be '%Y', and I need the sum of the <em>units</em> field for each accountID in transunit to be greater than 0. </p> <p>I can join the two tables, but i'm struggling with using the two conditions to filter the data.</p> <p>The only columns I would like to return from the 2 tables is:</p> <ol> <li>accountID</li> <li>clientID</li> <li>SUM(units)</li> </ol> <p>Here's what I have so far...</p> <pre><code>select trans.accountID,trans.clientID from inner join transUnit on trans.AccountID = transUnit.accountID where trans.IsAllocated like '%Y%' </code></pre> <p>This joins the two tables, and uses 1 condition.</p> <p>I have the 2nd query which sums all records in transunits table by accountID. 1 account ID can have multiple units records. I am just looking for accountID which have SUM greater than 0. </p> <pre><code>select count(*), sum(units) as Sumunits,accountid from transunit group by accountid </code></pre> <p>Now the tricky part, how do I join these two queries together, and filter so that I only display when SUM is greater than 0.</p> <p>Any help would be much appreciated, thanks.</p>