SQL query lookup 2 values from another table

Question

I have 3 tables:

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)

I want to do a query that shows all the records from the JCTransactions table but with Job description, cost code description, and top level cost code description.

For example:

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

I'm having trouble getting the TopLevelCostCode and their descriptions into the results.

Here's my code so far which works well except it doesn't have the last 2 column that I want:

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

how do you know there will be exactly two rows in JCCostCode for each JCJob? — Jasen, May 31 '15 at 02:54
There doesn't just have to be 2 rows of JCCostCode for each JCJob. A JCJob can have multiple JCCostCode. But each JCCostCode will have a row that ends with -000 as its top level cost code. (e.g. 01-000 is the top level code for 01-150). To clarify, the conditions after WHERE are put in by MS Query, I didn't write those queries so it may be incorrect. — martin, May 31 '15 at 03:14

score 0 · Answer 1 · answered May 31 '15 at 03:03

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

SQL query lookup 2 values from another table

1 Answers1