SQL subquery within an outer join

[ghbeers]

I'm having a hard time getting joins to communicate with each other when I have a subquery in one of them. For example I have the below query and I need the term in the 2nd outer join to equal the term prompted for in the first join. And of course, the subquery doesn't see the prompted for term value. How do I get around this.


select T1.id, T1.year, T1.term, T1.other_stuff, TH.term, A.CHARGES
from table1 T1

left outer join t_hist TH on T1.id = TH.id
and TH.term = <user prompted for term>

left outer join (select T2.id CASE when (sum(TH2.amt) > 0
and (TH2.code in ('X1','Y1')))
then (sum(TH2.amt))
else 0.0
end as 'CHARGES'
FROM table1 T2, t_hist TH2
WHERE T2.id = TH2.id
AND TH2.term = TH.term <<<< Need to match TH.term from above join
GROUP BY T2.id, TH2.code) A
ON T2.id = A.id

Thank you.

 

[simian336]

Create a variable and save the value of the term to the variable, then run your query using that variable.

Simi

 

[simian336]

declare @THterm varchar(50)

set @THterm = (Select thing from table)

select T1.id, T1.year, T1.term, T1.other_stuff, TH.term, A.CHARGES
from table1 T1

left outer join t_hist TH on T1.id = TH.id
and TH.term = @THterm

left outer join (select T2.id CASE when (sum(TH2.amt) > 0
and (TH2.code in ('X1','Y1')))
then (sum(TH2.amt))
else 0.0
end as 'CHARGES'
FROM table1 T2, t_hist TH2
WHERE T2.id = TH2.id
AND TH2.term = @THterm
GROUP BY T2.id, TH2.code) A
ON T2.id = A.id


Simi

 

[ghbeers]

This is similar to what I had originally, but I was trying to get away from creating a stored procedure, something I have to do before I can put this report into InfoMaker. If there is no other way, I will go back to doing it the way I previously had it structured.

Thank you.

 

[Olaf Doschke]

Without reading all the details, I just want to point out: When you add conditions on the two linked tables into a where clause any outer join turns into an inner join, as fulfilling the where condition automatically means there must be a join match in the first place, so that condition can be checked. Additional conditions are best put within the join (ON) condition, so they just contribute which records to join or not join, it doesn't filter out records without a match.

Bye, Olaf.