title: joining multiple table in ms sql server
body: <p><a href="https://i.stack.imgur.com/yrYtD.png" rel="nofollow noreferrer">![expected result that i want
  ]<a href="https://i.stack.imgur.com/yrYtD.png" rel="nofollow noreferrer">1</a></a>    </p>
  
  <pre><code>select 
  bd.BuildingID , 
  bd.[Building Description] , 
  fl.Floor , 
  rm.Room,
  rm.[Room Description],
  rm.[Room Type],
  asse.AssetName,
  cmp.ComponentName
  from Buildings bd 
  join Floors fl
  on bd.BLDID = fl.BLDID
  join Rooms rm
  on fl.FLID = rm.FLID
  join Assets asse 
  join Components cmp
  on cmp.AssetID=asse.AssetID;
  </code></pre>
  
  <p>I am trying to join multiple tables in ms sql server.
  I have 5 tables:</p>
  
  <ol>
  <li>buildings </li>
  <li>floors </li>
  <li>rooms </li>
  <li>assets </li>
  <li>components</li>
  </ol>
  
  <p>I want records from multiple tables.</p>
  
  <ul>
  <li>I joined <code>bd.BLDID = fl.BLDID</code> as I want <code>bd.BuildingID</code> and 
  <code>bd.[Building Description]</code> and both of them have common <code>BLID</code>.</li>
  <li>I joined <code>fl.FLID = rm.FLID</code> as I want <code>rm.Room</code>, <code>rm.[Room Description]</code>, and <code>rm.[Room_Type]</code></li>
  <li>I want to join asset and components as well as I want <code>asse.AssetName</code> and
  <code>cmp.ComponentName</code> (both tables have a common <code>AssetID</code>) </li>
  </ul>
  
  <p>I know the query above won't work because of the last 2 lines.</p>
  
  <p>Can anyone suggest how this join could be accomplished?</p>