Posted by Erland Sommarskog on 06/24/06 08:36

germanshorthairpointer@gmail.com (germanshorthairpointer@gmail.com) writes:
>> Joined to what?
>
> I would like YR_CDE and TRM_CDE added to my view (if a respective
> ID_NUM is in the FACULTY table)

Unfortunately that tells me very little. When you join is an operation
you perform between two tables. All I know is that you have a table
FACULTY, but I don't know how it relates to the other tables you have.

> ID_NUM is the primary key in the FACULTY table. However, each ID_NUM
> may have many different YR_CDE and TRM_CDE in the FACULTY table. The
> row with the latest JOB_TIME for a respective ID_NUM contains the
> latest Year (YR_CDE) and Term code (TRM_CDE) that a faculty member has
> taught. Does that help any? Thanks for your help!!!

I'm sorry, but this is contracdictory. First you say that ID_NUM is the
primary key of FACULTY. This means that this is a unique value. That
is there is at most one row for a certain value of ID_NUM. Because that
is what "primary key" means: one more columns that togather uniquely
identifies a row.

Next you say that "row with the latest JOB_TIME for a respective ID_NUM".
This indicates that more than one row for the same ID_NUM. In such
ID_NUM cannot be a primary key on its on.

But (ID_NUM, JOB_TIME) could be the composite key. In such case the
fragment in my previous post should give you what you want:

JOIN FACULTY F ON ?.ID_NUM = F.ID_NUM
JOIN (SELECT ID_NUM, maxtime = MAX(JOB_TIME)
FROM FACULTY
GROUP BY ID_NUM) AS FMAX ON F.ID_NUM = FMAX.ID_NUM
AND F.JOB_TIME = FMAX.maxtime

You would have to replace the question mark with table in the
view that FACULTY is related to.

If this does not work out, I suggest that you post the CREATE TABLE
statements for your tables - including definitions of primary keys
and foreign keys.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]