Joins giving me trouble

Transact-SQL
1

Here's the deal --

I need a table to show me certain columns only for those students that meet a certain criteria. This is done through a series of joins. All of my table works perfectly, except for the last join I need.

delete from Manual.dbo.FedFundStudents

insert into Manual.dbo.FedFundStudents (cwid,FN,MI,LN,awardtype,amount,POE)

select distinct alternate_id ,
first_name,
middle_init,
last_name,
fund_name,
scheduled_amount,
poe_token

from student s
join stu_award_year say on s.student_token=say.student_token
join stu_award sa on say.stu_award_year_token=sa.stu_award_year_token
join stu_award_transactions sat on sa.stu_award_token = sat.stu_award_token
join user_string us1 on say.stu_award_year_token=us1.stu_award_year_token
join user_int ui on say.stu_award_year_token=ui.stu_award_year_token

where say.award_year_token > 2013
and alternate_id is not null
and scheduled_amount >0
and (fund_ay_token='15666' or
fund_ay_token='16304' or
fund_ay_token='15667' or
fund_ay_token='15663' or
fund_ay_token='16276' or
fund_ay_token='16277' or
fund_ay_token='16274' or
fund_ay_token='16275' or
fund_ay_token='16361')

select * from Manual.dbo.FedFundStudents
order by CWID, AwardType, POE

I want to replace all of the "fund_ay_token=''" where clauses with one clause that says
"where fund_source='f'". The fund_source column comes from a table called Funds, which I thought I could join using: join funds f1 on say.stu_award_year_token=f1.stu_award_year_token

I thought it should look like this:

delete from Manual.dbo.FedFundStudents

insert into Manual.dbo.FedFundStudents (cwid,FN,MI,LN,awardtype,amount,POE)

select distinct alternate_id ,
first_name,
middle_init,
last_name,
fund_name,
scheduled_amount,
poe_token

from student s
join stu_award_year say on s.student_token=say.student_token
join stu_award sa on say.stu_award_year_token=sa.stu_award_year_token
join stu_award_transactions sat on sa.stu_award_token = sat.stu_award_token
join user_string us1 on say.stu_award_year_token=us1.stu_award_year_token
join user_int ui on say.stu_award_year_token=ui.stu_award_year_token
join funds f1 on say.award_year_token=f1.award_year_token

where say.award_year_token > 2013
and alternate_id is not null
and scheduled_amount >0
and fund_source='f'

select * from Manual.dbo.FedFundStudents
order by CWID, AwardType, POE

However, my return is really all records where a fund_source exists, not ONLY those where fund_source='f'

Any guidance?

THANKS!

2

Could you show us some sample data of what the first query is displaying and some sample data of what the second query is displaying, making sure to point out which ones are expected and which ones you don't want?

I'm not seeing an issue with what you posted, so I need to see data. But I might be missing something.

3

Tara,

Thanks for your reply.

For clarity stake, the "fund_ay_token" is what I'm using to get only the "Award Types" I need, which you'll see in the returned results. (I'm not able to post photos as a new user so I have to link it up).

Here's what it looks like when calling for each "fund_ay_token='xxxxx'" individually -- (what it's supposed to look like):

Box

And here's what it's looking like when I call for "fund_source='f'"

Box

In the second using "fund_source='f'", you'll see Award Types such as "Private Loan" and "BAFB TA" -- those are not 'f' fund sources, they're actually "fund_source='p'".

4

Tara,

I think I've solved it. I'm not quite sure why, but the join I was using was not joining correctly behind the scenes, so I compared two different tables and found another column that they had in common and switched the join. Now it's working perfectly.

Thanks again for your input and willingness to help!

1 Like
5

Is it possible to show us what you changed via the table joins? I am running into something very similar and am looking for another column to use as my join. Thanks!!

7

Taz,

Here's what the query ended up looking like, if it helps at all:

insert into Manual.dbo.FedFundStudents (cwid,FN,MI,LN,awardtype,amount,POE)

select distinct alternate_id ,
	            first_name,
	            middle_init,
	            last_name,
	            fund_name,
	            scheduled_amount,
	            poe_token
	
from student s
join stu_award_year say on s.student_token=say.student_token 
join stu_award sa on say.stu_award_year_token=sa.stu_award_year_token 
join stu_award_transactions sat on sa.stu_award_token = sat.stu_award_token
join funds f1 on sa.fund_name=f1.fund_short_name

where say.award_year_token > 2013 --set to one year less than you're searching for
and (poe_token='76' or --update POE token for the POE/years you want to search
     poe_token='77' or 
     poe_token='78' or 
     poe_token='79')
and alternate_id is not null
and scheduled_amount >0
and fund_source='f'

select * from Manual.dbo.FedFundStudents
order by CWID, AwardType, POE
8

I'll check it all out. Thanks for the quick reply and for sharing!!!