We help IT Professionals succeed at work.
Get Started

Access Not correlating subquery correctly

656 Views
Last Modified: 2012-06-27
myTable has a unique ID column and an EmployeeID column that allows duplicates. The following query is expected to return 1 row for each employee and does exactly that in most SQL databases.  But in Access (2007), it only returns 1 employee row.  The cause has to do with the T1 alias, which confuses Access.   If you change or remove the capitalized T1 alias, then the query works fine.

select * from mytable as t1
where id = (select max(id) from (SELECT * FROM MYTABLE AS T1) as t2
where  t1.[employeeid] = t2.[employeeid]);

To my understanding the capitalized T1 alias is out of scope for the outer where clause on employeeid and, therefore, should not be misinterpreted. Is Access out of compliance with the SQL standard or am I missing something?

Please do not answer with suggestions about how to "fix" the SQL and, yes, I know the inner most select is really unnecessary in this simplified example.

 Thanks









   
create table MyTable (id int, employeeid int, tries int);

	insert into MyTable  select 1, 1, 1;
	insert into MyTable  select 2, 1, 2;
	insert into MyTable  select 3, 1, 4;
	insert into MyTable  select 4, 1, 4;
	insert into MyTable  select 5, 2, 2;
	insert into MyTable  select 6, 1, 2;
	insert into MyTable  select 7, 2, 3;
	insert into MyTable  select 8, 3, 1;

--this produces 1 row per employee
select * from mytable as t1
where id = (select max(id) from (SELECT * FROM MYTABLE AS T1) as t2
where  t1.[employeeid] = t2.[employeeid]);

--this produces 1 employee row
select * from mytable as t1
where id = (select max(id) from (SELECT * FROM MYTABLE) as t2
where  t1.[employeeid] = t2.[employeeid]);

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This problem has been solved!
Unlock 5 Answers and 31 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE