I'm not even sure how to explain my problem. Hopefully y'all will understand what I'm talking about and can help.
I have a couple of tables with a fairly simple join. Something like this:
LEFT OUTER JOIN Table2
ON Table1.Field1 = Table2.Field1
AND Table1.Field2 = Table2.Field2
The problem is that Field2 may not be what Field2 is supposed to be. That is, if Field 2 is equal to Field3, then fine. However, if not, then it has to be something else.
Field2 Is Field3 Is Field2 Needs to be
FRED FRED FRED
FRED ETHYL LUCY
ADAM ADAM ADAM
ADAM EVE LILITH
And so on
Basically anytime Field2 <> Field3 it needs to be something else (a known value depending on the actual value of Field2). There are only four of these, and they will never change.
As far as the SELECT section of the query, I know how to display the correct value. However, I am stumped when it comes to the join. Specifically the last part (AND Table1.Field2 = Table2.Field2). I mean, if Field2 = Field3, then it's fine, but if Field2 <> Field3, then what do I do?
Hopefully someone understands and can help.