I have a straight forward sql statement that joins a few tables to get some metadata about the items in a container. The issue I have is that tableTwo now has a field that supersedes productid (let's call it masterProductId) if it's populated and we would therefor want to join one.productid on that column instead for that row. If masterProductId is not used it's normally left empty, but not null. I'm not sure how to handle the potentially different joins in one statement. Here's the bones of the statement without an attempt to add in masterProductId

```
select distinct one.code from tableOne one
inner join tableTwo two on two.productid = one.productid
inner join tableThree three on three.itemguid = two.itemguid
where three.wrhscontrid = @containerId
and one.statecode = '' 

```