SQLTeam.com | Weblogs | Forums

Left Join Not Working

I have a query which is using left join but it is not pulling the information which does not match the table on the right. This is my query:

SELECT
e.actnumst ContractReference,
'JAD' Currency,
case month(getdate()) when 1
then o.PERDBLNC
when 2
then o.PERDBLNC
when 3
then o.PERDBLNC
when 4
then o.PERIODID
when 5
then o.PERDBLNC
when 6
then o.PERDBLNC
when 7
then o.PERDBLNC
when 8
then o.PERDBLNC
when 9
then o.PERDBLNC
when 10
then o.PERDBLNC
when 11
then o.PERDBLNC
when 12
then o.PERDBLNC end PeriodBalance,
m.actdescr AccountNature,
GETDATE() BalanceSheetDate,
YEAR1,
periodid
FROM
gl00105 e
INNER JOIN gl00100 m ON m.actindx = e.ACTINDX
LEFT JOIN gl10110 o ON m.ACTINDX = o.ACTINDX and o.PERIODID = month(getdate())
where m.ACCTTYPE = 1 and year1 = year(getdate())
order by e.ACTNUMST

The records are only returned when there is a value in the GL10110 table. I would like all values in GL00105 table listed.

I have to assume that the column 'year1' comes from the GL10110 table. If that is true (cannot tell because you didn't use the table alias) then your WHERE clause is filtering out any row that doesn't match the criteria...

A row that doesn't match would have a NULL value - and a NULL value is not equal to year(getdate()).