INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL subquery within an outer join

SQL subquery within an outer join

(OP)
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.



RE: SQL subquery within an outer join

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

Simi

RE: SQL subquery within an outer join

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

RE: SQL subquery within an outer join

(OP)
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.

RE: SQL subquery within an outer join

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close