Combining LEFT JOIN and MAX

i've got two tables:

table: PEOPLE
columns: ID, NAME

table: TESTS
columns: ID, TEST_NUM, RESULT

rules:
1. one person can have from zero to many tests.
2. i want to see one row for each person, regardless of whether they have any tests or not.
3. i only want to see the most recent (highest numbered) test.

SQL:
i'm able to return all the rows using a LEFT JOIN, but i still need a way to only return the most recent test.

SELECT A.ID, A.NAME,
B.TEST_NUM, B.RESULT
FROM PEOPLE A
LEFT JOIN TESTS B
ON A.ID = B.ID

here's what's in the tables:

PEOPLE:
ID NAME
-- --
1 JOE
2 BOB
3 JIM
4 JOHN
5 RALPH
6 STEVE
7 MARY
8 BETH
9 SUE
10 KIM

TESTS:
ID TEST_NUM RESULT
-- --- --
1 1 PASS
1 2 PASS
2 1 FAIL
3 1 FAIL
4 1 PASS
4 2 FAIL
4 3 FAIL
6 1 FAIL
8 1 PASS
9 1 PASS

here's what the query currently returns:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 1 PASS
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 1 PASS
4 JOHN 2 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIM

here's what i want it to return:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIMtry this --select a.id
, a.name
, b.test_num
, b.result
from people a
left
join tests b
on a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||i get a syntax error with that.

it doesn't tell me why, maybe it doesn't like the 'and' in the 'join'?|||what is the syntax error?

oh, and perhaps you could also let us know which database you're using|||the real database will be DB2, but i'm testing on a mock access db for the time being. i get the same results in each.

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ''.

i only got this error after i added this:

and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||try this --

on (
a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)
)
you realize that sql is never portable between different databases, right?|||it's been my experience that basic SQL is portable. it's just the more complicated functions that aren't.

DB2 Error:

ODBC Error Code = 37000 (Syntax error or access violation)
[StarQuest][StarSQL ODBC Driver]SQL syntax error, .

Access Error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ' a.id = b.id and b.test_num = (select max(test_num) from tests where id = a.id) '.

it doesn't seem to like those outer parenthesis.|||select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, null
, null
from people a
left
join tests b
on a.id = b.id
and b.id is null|||DB2 doesn't seem to like the nulls.

access had a problem with the join, but who cares about access?

ODBC Error Code = 42703 ()

[StarQuest][StarSQL ODBC Driver][DB2]NULL IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE|||okay, for the second subselect, try:select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from ...|||oops, change the and in the last line to where|||hey, i think that worked.

there's no error at least.

some of my test data got purged overnight, so i'm going to repopulate the tables and give it a shot.

i'll post back when i do.

akosz

ps - don't bother with that devshed forum. i cross posted when this first started to see if anybody anywhere had any answers. apparently you do :)|||well that did the trick!

thanks a million for getting me through that one. :)

the bad news is, it looks like we're allowing duplicate id's in the PEOPLE table. a timestamp will differentiate between them and allow for a primary key. i really don't know how they want me to accomodate for this as it relates to the application i'm developing.

it's going to be a long afternoon...

akosz

btw - there are actually quite a few more columns and tables than what you see here, i just scaled it down to get at the syntax.|||now i need to add a WHERE (or an AND) clause to this so that i only get certain matches in the PEOPLE table.

here's the query:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null

here's what i need to add:

WHERE a.id IN (123,999,343)

i tried adding this as an AND clause after the first WHERE but it only brough back one row instead of 3.|||nevermind, i just needed some coffee :)

it's:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id
and a.id IN (123,999,343))
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null