SQL-запрос поиска 2 значения из другой таблицы

Question

У меня есть 3 таблицы:

1) JCTransactions
Job         CostCode    Date        Amount
36801-001   01-150      1/8/2015    55.00
36801-001   02-240      2/6/2015    26.00

2) JCCostCode
Job         CostCode    Description
36801-001   01-000      Land
36801-001   01-150      Land Legal Fees
36801-001   02-000      Holding Costs
36801-001   02-240      Property Taxes

3) JCJob
Job         Description
36801-001   BusinessCenter1
36901-001   BusinessCenter2(NotYetStarted)

Я хочу сделать запрос, который показывает все записи из таблицы JCTransactions, но с описанием задания, описанием кода затрат и описанием кода затрат верхнего уровня.

Например:

Job         CostCode    Date        Amount  JobDesc         CostCodeDesc    TopLevelCostCode    TopLevelCodeDesc
36801-001   01-150      1/8/2015    55.00   BusinessCenter1 Land Legal Fees 01-000              Land
36801-001   02-240      2/6/2015    26.00   BusinessCenter1 Property Taxes  02-000              Holding Costs

У меня возникают проблемы при получении TopLevelCostCode и их описания в результатах.

Вот мой код, который работает хорошо, за исключением того, что у него нет последних 2 столбцов, которые я хочу:

SELECT JCTRANSACTIONS.Job,
JCTRANSACTIONS.CostCode,
JCTRANSACTIONS.Date,
JCTRANSACTIONS.Amount,
JCJOB.Description,
JCCOSTCODE.Description
FROM "\\Network\".JCCOSTCODE JCCOSTCODE,
"\\ Network \".JCJOB JCJOB,
"\\ Network \".JCTRANSACTIONS JCTRANSACTIONS
WHERE 
JCJOB.Job = JCTRANSACTIONS.Job AND 
JCCOSTCODE.Cost_Code = JCTRANSACTIONS.Cost_Code AND 
JCCOSTCODE.Job = JCJOB.Job AND 
JCCOSTCODE.Job = JCTRANSACTIONS.Job

Answer 1

select * from JCCOSTCODE
select * from JCJOB 
select * from JCTRANSACTIONS

select Res1.job,Res1.costcode,res1.date,res1.amount,res1.jobdesc,res1.costdesc,res2.topLevelCode,res2.TopLevelCodeDesc from (
SELECT  JCTRANSACTIONS.Job,
JCTRANSACTIONS.CostCode,
JCTRANSACTIONS.Date,
JCTRANSACTIONS.Amount,
JCJOB.Description jobdesc,
JCCOSTCODE.Description as costdesc ,
row_number() over(order by JCTRANSACTIONS.CostCode) rowid
FROM JCCOSTCODE JCCOSTCODE
inner join JCJOB JCJOB on JCCOSTCODE.Job = JCJOB.Job
inner join   JCTRANSACTIONS JCTRANSACTIONS on JCJOB.Job = JCTRANSACTIONS.Job and JCCOSTCODE.CostCode = JCTRANSACTIONS.CostCode) Res1 inner join (
select costcode as topLevelCode, Description as TopLevelCodeDesc, ROW_NUMBER() over (order by costcode ) rowid from JCCOSTCODE where costcode not in (select costcode from JCTRANSACTIONS)) res2
on Res1.rowid = Res2.rowid