Simple inner join - why isn't this working?

[AdaHacker]

After staring at it for an hour, I finally solved this problem, but I wanted to see if anyone could tell me why it was happening. I have a query with tyhe following (abreviated) SQL:

SELECT ...
FROM ObjectTotals INNER JOIN FMS_CODE_DESC ON ObjectTotals.object = FMS_CODE_DESC.FMS_OBJ;

Now, in this query ObjectTotals is a query and FMS_OBJECT_CODE is a table. The SQL for ObjectTotals is:

SELECT object, [snow code], sum(amount) AS totamt
FROM fms_tran
WHERE [r/b code]=0
GROUP BY [snow code], object;

Seems simple enough to me. The problem was, my first query didn't return any rows. I ran ObjectTotals on its own and compared it to FMS_OBJECT_DESC, and there were many records that matched on the join column. According to everything I know about SQL, this means that there's no way the original query could return nothing.

Well, after playing with it for a while, I discovered that if I changed the GROUP BY clause in ObjectTotals to

GROUP BY object, [snow code]

then everything worked perfectly.

My question is: WHY?!?!? Am I missing some subtle semantic quirk of SQL, or is the Access 97 SQL interpreter just a peice of junk? I mean, if I take the original SQL for ObjectTotals and add

ORDER BY object

at the end, it will return the exact same rows in the exact same order as if I change the GROUP BY clause, but still doesn't work in my original query. I thought that as long as they returned the same rows, it didn't matter what the underlying SQL was. Apparently I was wrong, but can someone please explain why?

 

[AdaHacker]

Ack. I really need to practice my proof reading. For FMS_OBJECT_CODE and FMS_OBJECT_DESC, read FMS_CODE_DESC.

 

[cmmrfrds]

I believe object is a keyword in Access. Maybe it does not get interpreted as an ojbect if some more code follows. Change the name and try your original code.

 

[AdaHacker]

cmmrfrds,
Good guess, but no dice.

I did norrow the problem down a little. Just for the heck of it, I messed around a bit with the following query (cleaned up for your reading pleasure):

SELECT * 
FROM ObjectTotals INNER JOIN fms_tran
on ObjectTotals.object = fms_tran.object

I noticed that, when I used my original code for ObjectTotals, this query returned no rows. Again, it would work if I put object first in the GROUP BY clause. However, if I changed the join field to [snow code], then the query would work no matter what I did to the GROUP BY clause. Weird, huh? After messing around a little more, I discovered that if I move the [snow code] field to the first field in the SELECT and the second field in the GROUP BY, then I get no rows.
After adding another field and doing some further experimenting, it seems that this is only a problem when the join column is the first one in the SELECT. In that case, the join column also has to be the first one in the GROUP BY. If you move the join column farther down the list in the SELECT, everything is okay.
So, for the record, here are my findings:
When writing a query Q1 of the form

SELECT * FROM T1 INNER JOIN Q2 ON T1.join_col=Q2.join_col

where T1 is a table and Q2 is a query of the form

SELECT join_col, col1, sum(col2) 
FROM T GROUP BY join_col, col1

one of the following must hold:
1) The join_col field is the first field in the SELECT portion of Q2 and is also first field in the GROUP BY portion of Q2, or
2) the join_col field is not the first field in the SELECT portion of Q2.
If neither of these hold, Q1 will return no rows.
Wierd, huh? I'm assuming this must be a bug in Access 97, as I can't think of any reason why they would have designed it this way. Well, at least that's solved.