• Status: Solved
• Priority: Medium
• Security: Public
• Views: 287

# Table Joins (Dependant on Field Value)

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:
``````SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table2.Field4
FROM
Table1
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.

Example:
``````Field2 Is     Field3 Is     Field2 Needs to be
FRED          FRED          FRED
FRED          ETHYL         LUCY
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.

TIA
0
Clif
1 Solution

Commented:
If you know what the value of field2 is supposed to be then you can write:

SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table2.Field4
FROM
Table1
LEFT OUTER JOIN Table2
ON Table1.Field1 = Table2.Field1
AND Table1.Field2 =(CASE WHEN (Field2 = Field3) THEN  Table2.Field2 ELSE 'YOUR VALUE' END)
0

Principal ConsultantCommented:
It sounds like you are mixing Joins with the output of the query, but I am not sure what you want out of this.

The purpose of this join looks like you should only be joining the Table1.Field1 = Table2.Field1 and in your SELECT part of the statement, you would determine whether or not the output should be something else.

If you use the query above, you will truly get a LEFT join that will show the right hand table as NULL values if the Field2 <> Field3.  If that is what you want then the query above should help you.

If you are looking for the values in the Results then you could do something like this.

SELECT
Table1.Field1,
CASE WHEN Table2.Field2 = Table2.Field3 THEN Table2.Field2
WHEN Table2.Field2 != Table2.Field3 AND Table2.Field3 = 'ETHYL' THEN LUCY
WHEN Table2.Field2 != Table2.Field3 AND Table2.Field3 = 'EVE' THEN 'LILITH'
END
...
and so forth.  Then in your join, you would only do the Table1.Field1 = Table2.Field1
0

Author Commented:
This seems to work.  And by "work" I mean it doesn't blow up.  :)  The output is wrong, but I think it's something else.

I did not know one could use a CASE statement in the join.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.