title: Complicated sql query - select all landlords' properties and subtract costs in one query
body: <p>I have a table data structure similar to this: </p>
  
  <p><strong>Landlord table</strong></p>
  
  <pre><code>Id    Name        Email
  ```````````````````````
  1     J Johnson    ...
  2     R Kelly      ...
  </code></pre>
  
  <p><strong>Property table</strong></p>
  
  <pre><code>Id    Address    Rent    LandlordId
  ```````````````````````````````````
  1    ....        400    1
  2    ....        600    1
  3    ....        750    2
  </code></pre>
  
  <p><strong>Maintenance table</strong></p>
  
  <pre><code>Id    Details   Cost    MaintenanceDate    PropertyId
  `````````````````````````````````````````````````````
  1    ....        25     20/12/2012         1
  2    ....        120    22/12/2012         2
  3    ....        35     24/12/2012         3
  </code></pre>
  
  <p>Essentially, a Landlord has multiple properties.. Each month, I need to produce an invoice for the landlord which includes all his properties, all the maintenance done on his property. To calculate how much I need to pay to the landlord, I need to sum all his properties' Rent and subtract that with the sum of all his maintenance costs for that month.</p>
  
  <p>So, <em>amount payable to landlord L = Sum(Rent of properties of L) - Sum(maintenance costs on all properties of L during this month)</em></p>
  
  <p>I am using telerik reporting, thought I could achieve it with some clever grouping but that was a waste of my time so I am now going to try and achieve this with SQL and sub reports instead.</p>
  
  <p>The SQL query that I'm trying is this: </p>
  
  <pre><code> SELECT l.Name, p.[Address], p.Rent, c.Details, c.Cost,
  (select Rent From Property where Id = p.Id) - 
      (select SUM(cost) from CarriedOutJobs where PropertyId = p.Id) 
      as PayableToLandlord
  FROM Landlord l JOIN PROPERTY p ON p.LandlordId = l.Id
  LEFT OUTER JOIN Maintenance c ON c.PropertyId = p.Id
  ORDER BY l.Fullname
  </code></pre>
  
  <p>This doesn't seem to work properly as it produces multiple fields</p>
  
  <p>Because I am going to breakup the report into sub report, I thought I would just get landlord details first but I still need to calculate the amount payable to Landlord even in this case.. So I rewrote the query to this: </p>
  
  <pre><code>SELECT distinct l.Name,
  (SELECT SUM(Rent) FROM PROPERTY WHERE LandlordId = l.Id) - 
      (SELECT COALESCE(SUM(cost), 0)
      FROM CarriedOutJobs WHERE PropertyId = p.Id) AS     PayableToLandlord
  FROM Landlord l 
  JOIN PROPERTY p ON p.LandlordId = l.Id 
  ORDER BY l.Fullname
  </code></pre>
  
  <p>I thought this worked okay, but even though I have used distinct, this seems to produce a duplicate row with different <code>PayableToLandlord</code> amount and I can't seem to figure out why.</p>
  
  <p>Is there a way to select all landlords, their properties, and the amount payable to them in one query, please? </p>
  
  <p>I have removed date where clause for the sake of simplicity here.</p>
  
  <p>Thanks.</p>