title: Using conditions in joins
body: <p>I've below 3 tables from which need to fetch address for the records.</p>
  
  <p><strong>1. Address AD</strong>
   ---> It has all the address values(addrline1,addrline2,state,etc..) alongwith AddressID column</p>
  
  <p><strong>2. Address_Employee AE</strong>
   ---> It has EmpID,AddressID,AddressTypeID columns
  There can be multiple instances of same EmpID with different AddressID and AddressTypeID value as either 3 or 1</p>
  
  <p><strong>3. Address_Type AT</strong>
   ---> There are 2 rows, with AddressTypeID and AddressType columns. AddressTypeID value with 1 will be Home and 3 will be Postal.</p>
  
  <p>Need to fetch records in such a way that first need to look for Postal address, if not available then Home.</p>
  
  <p>I'm using the query:</p>
  
  <pre><code>SELECT (AD.sAddressLine1+' '+AD.sAddressLine2+' '+AD.sSuburb+' '+AD.sPostCode) AS Address
  from Address AD
  INNER JOIN Address_Employee AE ON AD.AddressID = AE.AddressID 
  INNER JOIN Address_Type AT ON AE.AddressTypeID= AT.AddressTypeID 
  </code></pre>
  
  <p>But how can I apply the condition: "first need to look for Postal address, if not available then Home."</p>
  
  <p>Please advise.</p>
  
  <p>Thanks, Krishna</p>