Posted by Erland Sommarskog on 06/22/06 22:12

germanshorthairpointer@gmail.com (germanshorthairpointer@gmail.com) writes:
> I've created a view, so my Perl connectors can easily script data
> into a meta directory. From the meta directory, data is scripted into
> two large directories OpenLDAP and AD. My initial question was
> rudimentary, because my goal was to understand how a join would work
> the way I needed it to.
>
> Now, to be more specific. I have another table from which I need to
> add two more fields to my view. Let's say I have a table called
> FACULTY. That table has YR_CDE, TRM_CDE, ID_NUM, and JOB_TIME. ID_NUM
> is the primary key and JOB_TIME is datetime. I need to add to my join
> YR_CDE and TRM_CDE from FACULTY for each ID_NUM. The latest JOB_TIME
> should determine which YR_CDE and TRM_CDE is joined.

Joined to what?

I'm afraid that I don't understand. If ID_NUM is a primary key how could
then JOB_TIME then determine which YR_CDE and TRM_CDE to join? Is it the
case that the primary key is (ID_NUM, JOB_TIME)?

Making this wild assumption, the join would look something like:

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

Since I don't know how FACULTY is related to the other tables, I
can't make it any better than this.

> CREATE VIEW dbo.META_VIEW
> AS
> SELECT TOP 100 PERCENT

Remove the TOP 100 PERCENT. It serves no purpose.



--
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]