LEFT JOIN shows null even when there is a match - sometimes

[mikecro]

Newbie on forum. Only semi-newbie on Access

We are still on Access 2003 and I am reporting from Project Server 2007 - so that is SQL Server. 2008 I think but could check.
Jet DLL version is 4.00.9511.0 (XP Sp3)

My target query will have GROUP BY but one column (resourceName) showed as null even though I know there are matches.
I can reproduce the problem without the GROUP BY - that does not seem to be the issue
Have seen simialr issues several times and was trying to live with it. But would like to solve it.

All of the source tables are in fact Views

1. Projects
2. Left Joins to Resource table using ProjectOwnerResourceUID
3. Left Joins to Task table using Task UID

INNER join works Ok and shows ResourceName with values
LEFT JOIN has the following strange behaviour:

1. remove Resourcename from query and just show the resourceID from resource Table. Values come up for ResourceUID (in teh resource table) showing a match
2. OR move Taskname from teh query. Values come up for ResourceName showing a match
3. OR (keep TaskName) and add a second field from teh Task table. Values come up from resourceName showing a match

I guess I have my workaround (3 above - add the 2nd field from Task) but what is going on?



MikeCro

Query SQl is

SELECT dbo_MSP_EpmProject_UserView.ProjectName, dbo_MSP_EpmProject_UserView.ProjectFinishDate, dbo_Custom_ts_Resource.ResourceUID, dbo_Custom_ts_Resource.ResourceName, dbo_MSP_EpmTask_UserView.TaskName
FROM (dbo_MSP_EpmProject_UserView LEFT JOIN dbo_MSP_EpmTask_UserView ON dbo_MSP_EpmProject_UserView.ProjectUID = dbo_MSP_EpmTask_UserView.ProjectUID) LEFT JOIN dbo_Custom_ts_Resource ON dbo_MSP_EpmProject_UserView.ProjectOwnerResourceUID = dbo_Custom_ts_Resource.ResourceUID;

[ItsMe]

I am not an expert in SQL or SQL Server.

If I am reading your post correct the DNS that your ODBC is using is based on a query of more than one table. It seems that you have tried GROUP BY in an effort to avoid viewing the symptom of the Cartesian Effect but, to no avail.

Perhaps creating a Query Object that looks only at the connection provided by the DNS and applying a GROUP BY statement along with a dynamic HAVING clause would be beneficial. I just do not understand why a field would indicate Null where it is expected to retrieve data. I would be counting records and comparing.

[rpeare]

Can you give an example of your source data and what you want the result to show. I'm having difficulty understanding the real issue here.

[ButtonMoon]

Not much to go on here. My understanding is that you get unexpected results depending on whether you remove a column from the SELECT list, even though no other logic changed. Is that correct? If so, I'd check for one of two things. The simple one first: did you miss out a comma in one of your SELECT statements or in a view? That's a very common mistake that turns what you thought was the column name into an alias for a different column - often difficult to spot.

The second thing to check is more subtle. There is a "bug"/feature in SQL Server where the column names in the view definition can become out of synch if an underlying table or view structure changes. Assuming you can do so without disrupting other users I suggest you recreate your views from source code to make sure they are what you think they should be.

[mikecro]

Thank all for the replies. For further info:

1. I do want to GROUP eventually. But that fails. Turns out the underlying unGROUPed query shows the same issue. Maybe I should not have mentioned it.
2. The data is not very relevant to the problem. One table joining to 2 others. INNER join works as INNER JOIN should. LEFT OUTER fails under the documented conditions
3. Got me thinking some more. Has there been a structure change?
   
   1. So I went into the Linked Table Manager and refreshed its view of the views I am reporting from (the responder asked to fresh the view definitions. But although I wrote them, they have been promoted to production and out of my control without change control). Made no difference
   2. I created a new Access DB file and imported the tables from SQL. Ran the same query. No problems.
   3. Copied the SQL to the old Access DB. Still had an issue
   4. So it seems like the old Access file is corruped or at least the wrong idea about the underlying tables - even though Linked Table did not fix it
   5. So I deleted the Links in Tables and relinked. All now works!

Are there any circumstances where Access somehow has the wrong definitions for the SQL tables/views and doesn't perform queries correctly? Somthing which Linked Table Manager cannot fix?

I did wonder if I had forgotten to set the unique field on import - maybe for just one of the tables. Is there a quick way of checking this for all of my imported tables?

MikeCro