Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    15

    Unanswered: Problem joining 2nd table to a select statement using a 'max' function.

    The problem, once I get the max ENTITY row, I need to join the ENTITY data from the 'max' selection to the ACCOUNT table to get the account information for this ENTITY row. I can not get my join to give me the correct results. Can anyone suggest how to structure the SELECT statment to accomplish this. I have not been successful yet!

    Note: Each entityID in the ENTITY table may have multiple rows, each indicated by the value in the 'occurence' column. I want to select the data for the most current ENTITY which would be the row that has the 'max' value in 'occurence' column.

    *This selects all current rows for the Entity table
    Code:
      SELECT entityID, idNum, idType, occurrence 
      FROM   ENTITY A
      WHERE A.entityID = 5          
      AND     A.history_flag = 'N'
    Data results before I use the 'max' function to get max(occurence)
    entityID idNum idType occurrence
    30331122 XD1 STD 000
    30331122 FD1 STD 001 (max) for 30331122
    40321123 XM1 STD 002 (max) for 45773323
    45773323 XD1 STD 000
    45773323 XD1 STD 001
    45773323 XD1 STD 002 (max) for 45773323

    *This selects the max occurrence row for the Entity
    Code:
      SELECT entityID, idNum, idType, occurrence 
      FROM  ENTITY A
      WHERE A.entityID = 5          
      AND   A.history_flag = 'N'          
      AND   A.occurrence = 
              (select max(B.occurrence)
               from  ENTITY B
               where B.entityID _id = A.entityID)
    Data results using the 'max' to get max(occurence)
    entityID idNum idType occurrence
    30331122 FD1 STD 001 (max) for 30331122
    40321123 XM1 STD 002 (max) for 45773323
    45773323 XD1 STD 002 (max) for 45773323

    So far, so good, I get the correct results I am looking for in selecting the Account row with the maximum occurence number.

    ** My problem is occuring when I try to join the ACCOUNT table to this SELECT:
    *This selects the max occurrence row for the Entity
    Code:
      SELECT A.entityID, A.idNum, A.idType, B.occurrence, B.accountID, accountNum 
      FROM  ENTITY A, ACCOUNT C
      WHERE A.entityID = 5          
      AND    A.history_flag = 'N'          
      AND    A.occurrence = 
              (select max(B.occurrence)
               from  ENTITY B
               where B.entityID _id = A.entityID) 
      AND A.entity_id = C.entity_id
    Last edited by ncsthbell; 11-07-11 at 16:30.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >FROM ENTITY A, ACCOUNT B

    you should NOT use same alias for different tables!

    >from ENTITY B

    what is "T2" below?
    (select max(T2.occurrence)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2010
    Posts
    15

    correction

    My mistake, getting myself confused, I have corrected the errors. Sorry.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >My mistake, getting myself confused, I have corrected the errors. Sorry.
    Corrected? Really?

    >FROM ENTITY A, ACCOUNT B
    >from ENTITY B

    B.ENTITY_ID belongs to which table above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2010
    Posts
    15

    previously corrected

    Sorry. When I edited previously, I realized that I had used the same table ID for the ACCOUNT table (ACCOUNT B) as I had used for the ENTITY table (ENTITY B). I changed the ACCOUNT table to use table identifier 'C'.

    I have it correct in my code, just mistyped when I entered into the text here.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have it correct in my code, just mistyped when I entered into the text here.
    I am sorry to see that COPY & PASTE are broken for you.

    Realize that since we don't have your tables or data, we can not run, test, or improve posted SQL.
    We don't know what you do, see or want.
    Good Luck with solving this mystery.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2005
    Posts
    57
    Code:
    SELECT A.entityID, A.idNum, A.idType, B.occurrence, B.accountID, accountNum
    You're selecting from table B but table B is not in scope - is this another 'edit' error - please cut and paste the actual query you're having problems with!!

    Code:
    ** My problem is occuring when I try to join the ACCOUNT table to this SELECT:
    And what exactly is the 'problem' you are having??

Posting Permissions

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