0

Here is my SQL Statement : 

 SELECT machine, 
   fixedassets.[serial no], 
   [date] 
FROM   fixedassets 
   LEFT OUTER JOIN maintenancerecord 
                ON fixedassets.id = maintenancerecord.id 
WHERE  areaid = @AreaID 
   AND [record type] = 'Service History'

It's Currently being used by one of my tables in a GridView(ASP.NET) to show the Machines , Serial No of the Machines & the Date they was last serviced ( From Maintenance Record Where Record Type = Service History ) When I execute this through my Datasource attached to the GridView , it doesn't display the machines , for Example if the Area ID = 4 Then i know that at least 4 machines should appear even though they haven't yet received a Service History ( From a insert in a diff table but that's not the point it should still return them ) ... So my actual question is : Even If the machines have no Service History they should still be Returned in the table because that's what a Left Outer Join does right?

Any more code that needs to be provided can / will do , just ask in the comments. Thanks you in advance!

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
Kallumasaurus
  • 271
  • 1
  • 4
  • 15

1 Answers1

2

[record type] should be check in the ON clause of the LEFT JOIN.

SELECT machine, 
       fixedassets.[serial no], 
       [date] 
FROM   fixedassets 
       LEFT OUTER JOIN maintenancerecord 
          ON fixedassets.id = maintenancerecord.id 
             AND maintenancerecord.[record type] = 'Service History'
WHERE  areaid = @AreaID
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • [Read more on this link](http://stackoverflow.com/questions/15706112/why-and-when-a-left-join-with-condition-in-where-clause-is-not-equivalent-to-the) – John Woo Apr 08 '14 at 10:40
  • You have guided my to my end goal , will mark as answer soon as possible. – Kallumasaurus Apr 08 '14 at 10:41