Q:

Correct way to write a SQL Query to combine two result sets with a condition, giving a distinct list back

I have 3 tables like the following:
tbl_risk (risk_ID, Title)
tbl_approval_history (approval_ID, risk_ID, status_ID, approver_ID)
tbl_users (user_ID, username)
A risk can have many approval history entries with different statuses. I need a query which will return a distinct list of risks with an approved by column, so output is
risk_ID, Title, Approved_by_username
BUT I only want the Approved_by_username column filled if the status_ID is a specific value. e.g. 5. Otherwise the value should be blank
Can anyone advise the best way to achieve this? I have tried the following (not added username yet), doesn't seem very efficient?:
select tbl_risk.risk_ID,   
    tbl_risk.title,  
    0 as Approver_ID  
from dbo.tbl_risk  
union all  
select distinct tbl_risk.risk_ID,   
    tbl_risk.title,   
    tbl_Approval_History.Approval_ID as Approver_ID  
from dbo.tbl_risk  
    ,dbo.tbl_Approval_History  
where dbo.tbl_Approval_History.risk_ID = tbl_risk.risk_ID  
and dbo.tbl_Approval_History.Status_ID = 5 

Any help is greatly appreciated.

A:

I think you want a left join:
select distinct r.risk_ID, r.title, ah.Approval_ID as Approver_ID  
from dbo.tbl_risk r left join
     dbo.tbl_Approval_History ah
     on ah.risk_ID = r.risk_ID and ah.Status_ID = 5 ;

Some comments:

I am guessing the distinct is unnecessary, because a risk would not have status 5 more than once.  If so, remove it.
Notice how the query is easier to read with table aliases.
Never use commas in the from clause.  Always use explicit join syntax.

