Doing a fancy select

[michael_andrews]

I have two tables of data that don't have a foreign key relation.

I basically want to join the data manually on a few points. In table a I have

CandidateId, TransactionDate, ExternalId

In table B I have

CandidateId, DateTaken, ExamCode

What I need to do is say give me the exam data where TableA.CandidateId = TableB.CandidateId

AND DateTaken > TransactionDate

AND IF ExternalId='foo' THEN ExamCode = 'SomeCode', ELSE IF ExternalId='bar' THEN ExamCode='SomeOtherId'

ORDER BY DateTaken

TAKE 1

Essentially. I want to make one table with the transaction data linked to the exam data on the candidate id and an if statement of hard coded when the id is this, then this exam id. But then order by the one closest to date taken (as there could be multiple) and only take the 1st one.

How would I go about that? I can provide the SQL equivalent if that would help

[chriscammers]

You have to do what you are trying to accomplish with a two step load.

First you left join based on the common fields(CandidateId) then to qualify the date condition you have to load the data to a new table. In your scenario you don't have a good primary key after the join so you won't be able to take care of the operation with an inner join so you'll create a new table. There were some parts of your pseudo code that I did not understand so I put them in as best I could figure out.