Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Posts
    50

    Unanswered: 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.

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    50
    i get a syntax error with that.

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the syntax error?

    oh, and perhaps you could also let us know which database you're using
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    50
    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)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Posts
    50
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Posts
    50
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, change the and in the last line to where
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2004
    Posts
    50
    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

  13. #13
    Join Date
    Sep 2004
    Posts
    50
    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.

  14. #14
    Join Date
    Sep 2004
    Posts
    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.

  15. #15
    Join Date
    Sep 2004
    Posts
    50
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •