SQLTeam.com | Weblogs | Forums

Problem Select Statement


#1

I'm using the following script:
select b.BEG_BAL, b.ACCt , b.FISCAL_YR
from BegBalance2015 b left outer join GLTrxPrdYear g on b.ACCT = g.GL_ACCOUNT and g.year=b.FISCAL_YR
group by b.beg_bal, b.acct, b.FISCAL_YR
order by b.ACCT
Which Returns

If I try to add a column from GLTrxPrdYear row 15 disappears because there are no trx's for that Acct for 2015, but I still need to show the account and its beginning balance. I though the left outer join would do the trick but its not.
Is there another way I can get this accomplished?


#2

What is the code you used for that has the problem?


#3

select b.BEG_BAL, b.ACCt , b.FISCAL_YR, g.trxamt
from BegBalance2015 b left outer join GLTrxPrdYear g on b.ACCT = g.GL_ACCOUNT and g.year=b.FISCAL_YR
group by b.beg_bal, b.acct, b.FISCAL_YR
order by b.ACCT

or

select b.BEG_BAL, b.ACCt , b.FISCAL_YR
from BegBalance2015 b left outer join GLTrxPrdYear g on b.ACCT = g.GL_ACCOUNT and g.year=b.FISCAL_YR
where g.period = 1
group by b.beg_bal, b.acct, b.FISCAL_YR
order by b.ACCT


#4

Which SQL statement is causing the problem? The second one has a problem because of the WHERE clause and the LEFT JOIN. Is the second statement causing the problem?


#5

My guess is that you need to use AND instead of WHERE for query 2

select b.BEG_BAL, b.ACCt , b.FISCAL_YR
from BegBalance2015 b left outer join GLTrxPrdYear g on b.ACCT = g.GL_ACCOUNT and g.year=b.FISCAL_YR
AND g.period = 1
group by b.beg_bal, b.acct, b.FISCAL_YR
order by b.ACCT