Left join not working (1 Viewer)

[KenHigg]

I have a table with all the dates for a period, say 1/1/2013 - 12/31/2013. I have a second table with transaction records with a date fld. I do I left join from the dates table to the trans table and want to see all days and if there are tran records I want to seen them for the given day. Problem is if there are no trans for the date I don't get any records for the day from the dates table - Are there some new settings I need to be looking at?

 

[pr2-eugin]

Are you sure you have not performed a RIGHT JOIN? If you use the Query wizard, it should help you identify the actual JOIN needed. Include all records from DatesTable and Only Records from the Transaction table WHERE Join fields are equal.

 

[KenHigg]

Yes - I double checked it and ran it all three ways.

 

[pr2-eugin]

Could you show (SQL View of) the Query you are using?

 

[CazB]

do you have any criteria on any of the fields you are selecting from the transactions table?
If you do, then that may limit the results you're getting...

as Paul said, we need to 'see' your SQL...

 

[KenHigg]

SELECT tblDates.rdate, tblOps_skdops_2.[Ac Number]
FROM tblDates LEFT JOIN tblOps_skdops_2 ON tblDates.rdate = tblOps_skdops_2.[Flight Origin Date]
WHERE (((tblOps_skdops_2.[Ac Number])="9005"))
ORDER BY tblDates.rdate;

 

[KenHigg]

I see the issue and it's working correctly. It is returning all date rows but then it filters those results based on the aircraft number...

Thanks for taking a look

 

[plog]

I don't know what the issue you saw and resolved is, but this is what I see: You do not have a LEFT JOIN query. When you use a WHERE clause on a field belonging to the table that is in the LEFT JOIN you have undone your LEFT JOIN and essentially made it an INNER JOIN.

1. FROM tblDates
2. LEFT JOIN tblOps_skdops_2 ON tblDates.rdate = tblOps_skdops_2.[Flight Origin Date]
3. WHERE (((tblOps_skdops_2.[Ac Number])="9005"))

1. Tells the query to bring in data from tblDates

2. Tells the query to bring in data from tblOps_skdops_2 if it matches tblDates, and if it doesn't show Null values where there aren't records in tblOps_skdops_2.

3. Tells the query to only show records from tblOps_skdops_2 that have have a specific value for [Ac number]. Which, undoes #2 which said to bring in Null values.

 

[KenHigg]

Got it - Thanks

 

[millhouse123]

So how do you limit your results if you can't have a where statement when left joining? I want all records from left table even if there is no match in right table but also want to limit records from right table to a specific date?

 

[plog]

In that instance you would leave it as a LEFT JOIN but change your WHERE clause to include your date or NULL values in the right table.

 

[millhouse123]

is this what you mean? This still does not work for me?

Select  distinct a.entity,a.gl_account,a.profit_center from(
SELECT DISTINCT  [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items] 
)a
LEFT JOIN  [Open Items]  AS [Open Items_1] ON (a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where  [Open Items_1].MonthEndDate=#8/31/2014#  or [Open Items_1].MonthEndDate is NULL

 

[plog]

Define "does not work". Error? Unexpected results?

Can you post your database?

 

[millhouse123]

My inner query returns 102 results but my outter query only returns 96. The records that are null on the right side are he ones not showing up in my query. Sorry I can't post the database but I am more than happy to answer any questions so that I can get this figured out. Thanks so much for your help

 

[plog]

You've referenced an inner query an outer query and a right side of a query--I see a query with a sub-query, I cannot determine what you are talking about when you mix terms like that. You've given your queries aliases, use those when referencing them--or just explicitly post code to show me what you mean when you reference "inner", "outer" and "right side".

And why did you hijack a 4 month old resolved thread?