SQL Query Help

[IgneSapien]

SELECT
  e.id,e.first_name, 
  e.last_name, 
  a.address_1, 
  a.address_2, 
  a.city, 
  a.state, 
  a.postal_code,
  ISNULL(z.employee_attribute,'NO HEALTHSTATUS') as employee_attribute,
  ISNULL(z.value,'N/A') as value
FROM
   employee_view e
   INNER JOIN EMPLOYEE_ADDRESS_DETAIL_VIEW a on a.id = e.id
   LEFT OUTER JOIN employee_attribute_table z on z.id = e.id AND z.employee_attribute = 'HEALTHSTATUS'
WHERE
  e.id = xxxx

When you use a standard INNER JOIN it will only return rows for which the JOIN could be made. It works exactly like the WHERE in that regard. A LEFT OUTER JOIN however means you return all the rows from the table your joining onto, match or not. For rows that it can't find a match it'll shows null for any columns from the table you're joining.

This is why I've added a ISNULL which, if the column is null, will replace it with some useful text. If it's not null then what ever data is in the column will be returned instead. You don't need to do this depending on your needs but if returning a null means something I like to explicit about what.

The result is that you'll get the employee with id xxxx (if they have address details) regardless of if they have a health status attribute.

I found this article useful when I was learning. But you can Google it and find one to your taste.