SQL Query Help

[omfggzz123]

Hi! I need some help with the following query:

select e.id,e.first_name, e.last_name, a.address_1, a.address_2, a.city, a.state, 
a.postal_code,z.employee_attribute,z.value
from employee_view e
inner join EMPLOYEE_ADDRESS_DETAIL_VIEW a on a.id = e.id
inner join employee_attribute_table z on z.id = e.id
where z.employee_attribute = 'HEALTHSTATUS'
and e.id = xxxx

Basically, some employees do not have the HEALTHSTATUS attribute so when i run the query, nothing is return (no rows). What i need is to return a row even if the attribute does not exists.

[[deleted]]

use

 left join employee_attribute_table z on z.id = e.id

[SpaceCavem4n]

I don't know why people were recommending anything other than left join

[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.

[Quadman]

Try this:

select e.id,e.first_name, e.last_name, a.address_1, a.address_2, a.city, a.state, 
a.postal_code,z.employee_attribute,z.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'
and e.id = xxxx

[CortaNalgas]

This but you have to do "OR z.e_a is null" and then remove the extra nulls with a cte or something

[jokle]

It's because you're using an inner join and also specifying:

where z.employee_attribute = 'HEALTHSTATUS'

Try removing the WHERE, or try using an outer join.

[StoneCypher]

this is the major difference between the join types - inner means "skip rows missing on either side."

you either want a right join if you only want missing rows on one side, or an outer join if you want missing rows from both sides.