Sub query to return same number of records and join i have this query

SELECT DISTINCT (a.AssetID) AS [AR BarCode],au.[Asset_barcode] AS [Audit BarCode],a.AssetRegisterID, a.Description AS [Asset Description],
      a.LocationRoomID,g.[RoomNo] AS [AR RoomBC], 
      h.Description AS [AR Building]
FROM dbo.AssetRegister AS a LEFT OUTER JOIN
     dbo.LocationRoom AS g ON g.LocationRoomID = a.LocationRoomID LEFT OUTERJOIN
     dbo.Audit AS au ON au.Asset_barcode = a.AssetID LEFT OUTER JOIN
     dbo.Building AS h ON h.BuildingID = g.BuildingID
ORDER BY a.AssetRegisterID ASC, g.[RoomNo] ASC


Which works and return the 29771 records i need but when i add au.[Room_barcode] i get 37633 records which i dont know what is happening.

I want to compare AssetID's from 2 tables dbo.AssetRegister and dbo.Audit. Also the RoomBarcodes. Because Assets in the dbo.AssetRegister is the base data and after the audit process assets were found in different rooms.

Please help
Simplify the problem. Find a register / room combo that has multiple barcodes:

SELECT a.AssetID, g.RoomNo, MIN(au.Room_barcode), MAX(au.Room_barcode)
FROM dbo.AssetRegister AS a 
  LEFT OUTER JOIN dbo.LocationRoom AS g 
  ON g.LocationRoomID = a.LocationRoomID 
  LEFT OUTER JOIN dbo.Audit AS au 
  ON au.Asset_barcode = a.AssetID
GROUP BY a.AssetID, g.RoomNo
HAVING COUNT(*) &gt; 1;


Then pick one of those AssetID / RoomNo combinations and look closer at the data:

SELECT a.AssetID, g.RoomNo, au.Room_barcode
FROM dbo.AssetRegister AS a 
  LEFT OUTER JOIN dbo.LocationRoom AS g 
  ON g.LocationRoomID = a.LocationRoomID
  AND g.RoomNo = &lt;room number from above&gt; 
  LEFT OUTER JOIN dbo.Audit AS au 
  ON au.Asset_barcode = a.AssetID
WHERE a.AssetID = &lt;asset ID from above&gt;;


Now, if you are getting multiple room barcodes back, you have to determine if that is valid or not (we can't tell you that, this is your data after all). If it is not valid, you need to fix the data. If it is valid, then you need to decide whether you want all the barcodes in the result or, if you just want one, which one? Build actual sample data from this single Asset/Room combo, add that data to your question, and explain what results you want from that sample data.

Don't try to reduce row counts by slapping DISTINCT in front of the column list, or increase row counts by changing inner joins to outer joins. Start simple and make sure you are collecting the right data rather than whether n row(s) affected looks right.