Combining LEFT JOIN and MAX

akosz · #1 (**permalink**) 09-23-04, 14:51

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.

Code:

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:

Code:

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:

Code:

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:

Code:

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	KIM

r937 · #2 (**permalink**) 09-23-04, 15:14

try this --

Code:

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)

akosz · #3 (**permalink**) 09-23-04, 15:21

i get a syntax error with that.

it doesn't tell me why, maybe it doesn't like the 'and' in the 'join'?

r937 · #4 (**permalink**) 09-23-04, 15:42

what is the syntax error?

oh, and perhaps you could also let us know which database you're using

akosz · #5 (**permalink**) 09-23-04, 15:45

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.

Code:

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:

Code:

and b.test_num
     = (select max(test_num)
          from tests
         where id = a.id)

r937 · #6 (**permalink**) 09-23-04, 15:49

try this --

Code:

    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?

akosz · #7 (**permalink**) 09-23-04, 16:03

it's been my experience that basic SQL is portable. it's just the more complicated functions that aren't.

DB2 Error:

Code:

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.

r937 · #8 (**permalink**) 09-23-04, 16:29

Code:

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

akosz · #9 (**permalink**) 09-24-04, 10:16

DB2 doesn't seem to like the nulls.

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

Code:

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

r937 · #10 (**permalink**) 09-24-04, 10:23

okay, for the second subselect, try:

Code:

select a.id
     , a.name
     , cast(null as integer) as test_num
     , cast(null as char(4)) as result
  from ...

r937 · #11 (**permalink**) 09-24-04, 10:36

oops, change the and in the last line to where

akosz · #12 (**permalink**) 09-24-04, 10:54

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

akosz · #13 (**permalink**) 09-24-04, 11:42

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.

akosz · #14 (**permalink**) 09-27-04, 11:50

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:

Code:

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:

Code:

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.

akosz · #15 (**permalink**) 09-27-04, 11:58

nevermind, i just needed some coffee

it's:

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech