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

Open in new window

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
ADAM          ADAM          ADAM
ADAM          EVE           LILITH
And so on

Open in new window

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
LVL 10
ClifAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itsdivyaksCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBAduck - Ben MillerPrincipal 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
ClifAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.