Can"t get a left join to work correctly

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, [B]![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = [B].AR) AND (A.DV = [B].DV) AND (A.BU =
[B].BU) AND (A.MCLASS = [B].MCL)
WHERE (([B]![NAME]="COMM"));

Re: Can"t get a left join to work correctly

I A and B are aliases for tables then you need to include the table
names before the aliases. If A and B are the table names then never
mind about the above. Try the syntax below in the Query sql window
(change the names of the tables of course)


SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS,
B.PD01 * A.PD01 As PD01 FROM tableA A LEFT JOIN tableB B ON A.AR = B.AR
AND A.DV = B.DV AND A.BU = B.BU AND A.MCLASS = B.MCL
WHERE B.NAME='COMM';



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Can"t get a left join to work correctly

On Nov 13, 10:56 am, SP wrote:
> 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, [B]![PD01]*[A]![PD01] AS
> PD01
> FROM A LEFT JOIN B ON (A.AR = [B].AR) AND (A.DV = [B].DV) AND (A.BU =
> [B].BU) AND (A.MCLASS = [B].MCL)
> WHERE (([B]![NAME]="COMM"));

the WHERE clause is removing all the records which do not have a value
= "COMM", and since those which have no matching row cannot have a
"comm" entry, they will not show in your result set.

You can build a query on table b alone, filtered for name = "comm" and
use this query instead of the table, so you don't need the where
clause in the left join query.

You can also try changing the where clause to
WHERE [B]![NAME]="COMM" OR [B]![NAME] is null;
note that this sometimes has undesirable results.