Left join problem

[e.badin]

Hi guys,

What the hell am I doing wrong ? This is really pissing me off. As simple as it may be, it got me realy frustrated because it doesn't work proper.

So...I have this 2 tables A,B. The combination Resource id / Cost account is the primary key. What I need is to get all the values from A and add the C column from B, where is the case.

Table A
--------------------------


Resource Id | Cost Account

Table B
---------------------------------
Resource ID | Cost Account | C

The query should be this:

SELECT A.*, B.C
FROM A LEFT JOIN B ON (A.[Cost Account] = B.[Cost Account]) AND (A.[Resource ID] = B.[Resource ID]);

The problem is that I get nothing in the C column for the combination Resource ID | Cost Account, when Cost Account is Null, even if in the B table there is data.

Please tell me I'm stupid I don't know how to use the Left Join....

Edit:
I checked the previous topics with null related issues but I still have trouble understand the outcome of the those disscusions.

[Rixxe]

 
SELECT a.*
FROM TableA as A
LEFT JOIN 
TableB as B
ON(A.[Cost Account] = B.[Cost Account]) AND (A.[Resource ID] = B.[Resource ID])
WHERE b.[Cost Account] is null

I'm sure you need a "WHERE [Field] is null" somewhere in a left join...

[weekend00]

null doesn't match null in a join query, just like null=null is false in expression;

that means you can not get result when the join field is null.

you can try to change the query to:
SELECT A.*, B.C FROM A LEFT JOIN B ON (nz(A.[Cost Account],"") = nz(B.[Cost Account],"")) AND (nz(A.[Resource ID],"") = nz(B.[Resource ID],""));


To Rixxie:
you query with where clause is to get the records only exist in tableA but not exist in tableB, this is not the OP wants.

[e.badin]

thanks... I changed the null tu "NA".... but I still can't understand why null<>null...

[weekend00]

null=null is false while null<>null is also false, you should not compare with null, 'cause null is nothing, you can not compare nothing. how can you compare when there is nothing?

[Rixxe]

Misread on my part, Thanks weekend.