Simple SQL Request - can't phrase it

Databases
1

My apologies; since I’m unaware of the terminology, I cannot phrase (thus I could not google to find on my own) what the situation is I’m having.

Windows, SQL 2005.

I want to pull back UNIQUE results (1 person no matter how many times he comes back) We have 3 tables, PERSONNEL, VALIDATIONS and RANK.

1 person can have MULTIPLE ranks, and we use RANK to determine if a user is no longer employed (go fig)

PERSON
> PersonnelID
> FirstName
> LastName

RANK
> PersonnelID
> vsRank
> EnteredDate

VALIDATIONS
> EntryID
> Description

It works like this. PERSON.PersonnelID links to RANK.PersonnelID to obtain all ranks of that user. We are ONLY interested in obtaining the MOST RECENT (by use of RANK.EnteredDate) Once obtained, the RANK.vsRank field links to the VALIDATION table’s VALIDATION.EntryID in order to get the DESCRIPTION (which is the actual rank)

So 1 person can have 3 ranks, let’s say:

#1 - Deputy (Jan 1, 2010)
#2 - Sergeant (May 1, 2010)
#3 - Resigned (May 30, 2010)

When I pull him up in my final query, I just want 1 record, which shows his latest rank (Resigned)

How do I do this?

2

sorry, there is no such column :stuck_out_tongue:

SELECT p.personnelid
     , [COLOR="Red"]v2.description AS name_suffix[/COLOR]
     , m.lastdate
     , v.description
  FROM person AS p
INNER
  JOIN ( SELECT personnelid
              , MAX(entereddate) AS lastdate
           FROM rank
         GROUP
             BY personnelid ) AS m
    ON m.personnelid = p.personnelid
INNER
  JOIN rank AS r
    ON r.personnelid = m.personnelid
   AND r.entereddate = m.lastdate
INNER
  JOIN validations AS v
    ON v.entryid = r.vsrank
[COLOR="red"]INNER
  JOIN validations AS v2
    ON v2.entryid = p.vsnamesuffix[/COLOR]
3

Nevermind, I got it. Day 1 mistake. :slight_smile: Thanks again.

4

That doesn’t seem to work.

It gives me exactly what I wanted…

…

wait a minute…

GO RUDY! Hey, for extra credit, what the HECK would I have used for keywords on what you did to have found that solution?

And DON’T say “Easy SQL examples” :slight_smile:

Thanks!

5

Hey Rudy, could I add one more thing to the query above?

I want to pull the person’s name suffix. The way that goes is:

Personnel.vsNameSuffix -> Validations.EntryID

But the value I want is Validations.EntryValue

I tried adding a “LEFT JOIN Validations AS v2 ON P.vsNameSuffix = V2.EntryID” and then adding “V2.EntryValue” in the Select List, but doing so returns some people who are Resigned. These people who are resigned were NOT being returned (properly) before I added the LEFT JOIN.

6

no idea, sorry

i just googled a few phrases and came up empty

it’s a fairly common scenario, though, as my log of similar queries shows:

7 
SELECT p.personnelid
     , m.lastdate
     , v.description
  FROM person AS p
INNER
  JOIN ( SELECT personnelid
              , MAX(entereddate) AS lastdate
           FROM rank
         GROUP
             BY personnelid ) AS m
    ON m.personnelid = p.personnelid
INNER
  JOIN rank AS r
    ON r.personnelid = m.personnelid
   AND r.entereddate = m.lastdate
INNER
  JOIN validations AS v
    ON v.entryid = r.vsrank

:cool: