469,282 Members | 1,692 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Can't get a left join to work correctly

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, [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"));

Nov 13 '07 #1
2 1401
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 ***
Nov 13 '07 #2
On Nov 13, 10:56 am, SP <s.pamur...@gmail.comwrote:
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.

Nov 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Petre Agenbag | last post: by
1 post views Thread by Chris Thompson | last post: by
9 posts views Thread by shanevanle | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.