Left join

[jerkyjerk]

Is it okay to use direct values in ON statement after the LEFT JOIN?

SELECT A.*, B.*,C.* 
 FROM ((A 
  LEFT JOIN B ON B.F1 = A.F1 AND B.F2 = A.F2) 
  LEFT JOIN C  ON [COLOR="#FF0000"]C.F3 = '201504' [/COLOR]AND C.F1 = A.F1 AND C.F2 = A.F2) 
    WHERE A.F1='0051004' AND A.F2='USD' AND A.F3='201505';

Problem here is on C.F3 = '201504' , insted of C.F3 = A.F3 I used C.F3 = '201504' .
'201504' will be generated from a function in my excel vba code.

Is there a way where I can use the function return value instead of the field name.
Or how can i correct this SELECT statement?

 

[xenou]

it might work in an on clause (it would in SQL Server - not sure about Access).

But as a rule of thumb in any database engine, that goes in the WHERE clause:

SELECT A.*, B.*,C.* 
 FROM ((A 
  LEFT JOIN B ON B.F1 = A.F1 AND B.F2 = A.F2) 
  LEFT JOIN C  ON C.F1 = A.F1 AND C.F2 = A.F2) 
    WHERE 
        A.F1='0051004' 
        AND A.F2='USD' 
        AND A.F3='201505'[COLOR="#FF0000"]
        AND C.F3 = '201504'[/COLOR];

Or if you have a function:

SELECT A.*, B.*,C.* 
 FROM ((A 
  LEFT JOIN B ON B.F1 = A.F1 AND B.F2 = A.F2) 
  LEFT JOIN C  ON C.F1 = A.F1 AND C.F2 = A.F2) 
    WHERE 
        A.F1='0051004' 
        AND A.F2='USD' 
        AND A.F3='201505'[COLOR="#FF0000"]
        AND C.F3 = MyFunction()[/COLOR];

 

[jerkyjerk]

it might work in an on clause (it would in SQL Server - not sure about Access).

But as a rule of thumb in any database engine, that goes in the WHERE clause:

SELECT A.*, B.*,C.* 
 FROM ((A 
  LEFT JOIN B ON B.F1 = A.F1 AND B.F2 = A.F2) 
  LEFT JOIN C  ON C.F1 = A.F1 AND C.F2 = A.F2) 
    WHERE 
        A.F1='0051004' 
        AND A.F2='USD' 
        AND A.F3='201505'[COLOR=#ff0000]
        AND C.F3 = '201504'[/COLOR];

Or if you have a function:

SELECT A.*, B.*,C.* 
 FROM ((A 
  LEFT JOIN B ON B.F1 = A.F1 AND B.F2 = A.F2) 
  LEFT JOIN C  ON C.F1 = A.F1 AND C.F2 = A.F2) 
    WHERE 
        A.F1='0051004' 
        AND A.F2='USD' 
        AND A.F3='201505'[COLOR=#ff0000]
        AND C.F3 = MyFunction()[/COLOR];

Thanks!

I tried to put it in the WHERE clause.
It works now..

I thought that conditions for the WHERE clause are only for the Original table, in this case Table A.
But I was wrong..