Left join problem

[ItsMe]

For some reason, when I read post #13. I thought I saw a mention of a calculation when there isn't.

I looked back over the thread and the only thing I can think of is that the AND operator is requiring the NZ function. Without the NZ function returning all of the records, it can not evaluate each statement in the expression. In other words, it must first retrieve all of the records and then evaluate each join, independently.

This is just a guess but would help explain why one LEFT join works over the other.

[therzakid]

Okay, so I tried something really simple:
SELECT SE_Base.CustID, SE_Base.Chipset_ID, SE_Base.Product, SE_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
FROM SE_Base LEFT JOIN Act_Base ON SE_Base.Product = Act_Base.Product;

This populates all Act_Base.Qty as Null values still


But, when I did the below joins instead, I got values (albeit wrong values b/c I need to join on 3 fields total):
got values:
SELECT SE_Base.CustID, SE_Base.Chipset_ID, SE_Base.Product, SE_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
FROM SE_Base LEFT JOIN Act_Base ON SE_Base.CustID= Act_Base.CustID;

SELECT SE_Base.CustID, SE_Base.Chipset_ID, SE_Base.Product, SE_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
FROM SE_Base LEFT JOIN Act_Base ON SE_Base.Qtr= Act_Base.Qtr;

[ItsMe]

If you compact and repair, then Zip down your DB, and upload it to this thread, using the "Go Advanced" button and the "Manage Files" button I will take a look at it. I can not make heads nor tales by your posts alone. I will need to take a look at thte DB too.

[therzakid]

Please look at the query called "right join". Attached is the workbook and thnaks for having a look.

[ItsMe]

Your query is based on other queries.

I am going to guess that this expression
(select Qtr_No from Qtr where Qtr_Date = SE.Quarter) AS Qtr

is going to make the difference between pulling all of the records from one query vs. pulling all of the records from the other query. In other words, if you do not pull all of the records from query "Act_Base" (where the expression is) you will need to employ the Nz() function.

[therzakid]

Did you try it? Why would you need to do NZ on the Product field though if you think it's the quarter field? I can't really understand this. Also, from the example you can see that the expression doesn't llimit the records, it just converts a date to a quarter.

[ItsMe]

No, I did not try it. Each query only returns one record so I will guess that there is not enough data to make a distinction. Besides, it is only a best guess. It is a good idea to understand why your code behaves the way it does. If it is not the expression in the QTR alias it is most likely an expression in one of the other columns within the query.

[therzakid]

I've tried removing the quarter expression and hard coded "Q2" into the query, it didn't fix anything though. It has nothing to do with the quarter expression imo. Like I said earlier, the problem is around the product field and is evidenced by the join working when NZ is used on the Product fiel. What else do you think it might be?

[ItsMe]

You said it yourself. It is the Left Join vs. Right join. I reiterated the point when I mentioned selecting ALL the records from one query is key. Add the fact that expressions/calcs do not play well with Null fields and I believe you have your answer.

As you can see, it is not simple. The query you created is not simple and is somewhat unconventional. Combine this with the fact there is not enough data in the sample you provided to duplicate the behavior, I will not be able to help. You are close. Follow the logic provided and keep looking at the results as you change the query.

I will say, again, it is important to understand why you get a specific behavior. I am not trying to discourage you from understanding why.

[therzakid]

Thanks for looking ItsMe. I wholeheartedly agree it's important to understand the why behind the behavior and that's the bit I'm after now, the part I was hoping you could provide some insight on. Thanks again for trying.

Anyone else maybe know why the joins behave differently and why product is being difficult to work with?