Do computation in select statement using a current row column

SELECT a, b, c, d, e FROM Table1 T1 INNER JOIN Table2 T2 ON T1.A = T2.ID
What is the name of the column which acts as your relationship in table2.
Infact why dont you paste your table structures here and we can go through writting you a join

you can embed the 2nd query within the select and reference the columns from the outer query like this...


select a, b, c, d, (select table2.e+ table1.a from table2 where table2.id = table1.c)
from table1 where f = 1

Below is my current query:
select id,agent_number 'Agent #',coverage_area 'Coverage Area', [Type] = CASE agent_type
WHEN 'S' THEN 'Skip'
WHEN 'R' THEN 'Recovery'
WHEN 'M' THEN 'Remarketing'
END
,city 'City',
[state] 'State',terms 'Terms', recovery_fee 'Recovery',closed_fee 'Closed',imp_client_fee 'Impound',
recovery_vol_fee 'Voluntary', imp_days_nocharge_fee 'Free Days', rating 'Rating'
from Agent
where agent_status = 1
and agent_number is not null
order by state

This is the query I need to do to create the 'New column' called '#Cases' in the query above:
(select count(distinct case_number) from [case] c, history h,
where c.id=h.case_id and
c.agent_id='current value of column 'a' above'
and  h.status=4) '# Cases'

so the relationship is agent_id in the case table to the id in the agent table...but I am only doing a count.

try this...  (assuming "a" is agent.id)

select id,agent_number 'Agent #',coverage_area 'Coverage Area', [Type] = CASE agent_type
WHEN 'S' THEN 'Skip'
WHEN 'R' THEN 'Recovery'
WHEN 'M' THEN 'Remarketing'
END
,city 'City',
[state] 'State',terms 'Terms', recovery_fee 'Recovery',closed_fee 'Closed',imp_client_fee 'Impound',
recovery_vol_fee 'Voluntary', imp_days_nocharge_fee 'Free Days', rating 'Rating',
(select count(distinct case_number) from [case] c, history h, 
where c.id=h.case_id and 
c.agent_id=Agent.id
and  h.status=4) '# Cases'
from Agent 
where agent_status = 1 
and agent_number is not null 
order by state

Select allOpen in new window

the "select count(distinct case_number..." fails because it doesn't know what 'Agent' is.
If I add the agent table to the select and do a join it willl give me the intersection of the tables.
I only want to do the calculation on the other table where the current value of 'id' is used in the subquery.

not sure if i am making sense on this...

maybe your database doesn't support subqueries of that form.
You didn't specify what db you were using, I assumed Oracle, but based on your syntax, it must be something else.   If the subquery syntax isn't supported in your db then you'll need to do a join  as suggested above

sorry. this is sql server.