Trouble making a left join work

[SP]

I have two tables, A & B. Table B has adjustment factors of three
types, "COMM" being one of them. I want a query that generates all
rows of table A, and those where certain fields match, I want the
value of A.PD01 to be multiplied by the the "COMM" factor.

I have the following SQL, but it only generates the records where the
fields match, not all of them.

SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, ![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = .AR) AND (A.DV = .DV) AND (A.BU =
.BU) AND (A.MCLASS = .MCL)
WHERE ((![NAME]="COMM"));

 

[Andy Hull]

Hi

Where the rows don't match the COMM value is Null so doesn't match your
where clause.

Try moving the where clause into the outer join like...

SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, ![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = .AR) AND (A.DV = .DV) AND (A.BU =
.BU) AND (A.MCLASS = .MCL) AND (![NAME]="COMM")


hth

Andy Hull