Posted by germanshorthairpointer@gmail.com on 06/22/06 16:50

Thanks David!

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.

Let me know if you need more info. I don't work with MS SQL
everyday, so forgive me if my question is not clear enough.

Grant

CREATE VIEW dbo.META_VIEW
AS
SELECT TOP 100 PERCENT
dbo.NAME_MASTER.ID_NUM AS person_id,
dbo.NAME_MASTER.LAST_NAME AS person_lname,
dbo.NAME_MASTER.FIRST_NAME AS person_fname,
dbo.NAME_MASTER.EMAIL_ADDRESS AS person_email,
dbo.ADDRESS_MASTER.ADDR_LINE_1 AS person_street,
dbo.ADDRESS_MASTER.ADDR_LINE_2 AS person_street2,
dbo.ADDRESS_MASTER.ADDR_LINE_3 AS person_street3,
dbo.ADDRESS_MASTER.CITY AS person_city,
dbo.ADDRESS_MASTER.STATE AS person_state,
dbo.ADDRESS_MASTER.ZIP AS person_zip,
dbo.ADDRESS_MASTER.COUNTRY AS person_country,
dbo.ADDRESS_MASTER.PHONE AS person_phone,
dbo.BIOGRAPH_MASTER.SSN AS person_ssn,
dbo.BIOGRAPH_MASTER.BIRTH_DTE AS person_dob,
dbo.STUDENT_MASTER.CURRENT_CLASS_CDE AS person_stu_year,
dbo.STUDENT_MASTER.CUR_STUD_DIV AS person_stu_div,
dbo.STUDENT_MASTER.MOST_RECNT_YR_ENR AS person_stu_enrolled,
dbo.STUDENT_MASTER.MOST_RECNT_TRM_ENR AS
person_stu_lastterm, dbo.TW_WEB_SECURITY.ACCESS_CDE AS person_password,
dbo.STUDENT_MASTER.TRM_HRS_ENROLLED AS person_stu_hours,
dbo.STUDENT_MASTER.TUITION_CDE AS person_stu_tuition,
dbo.ROOM_ASSIGN.ROOM_CDE AS person_stu_room,
dbo.ROOM_ASSIGN.BLDG_CDE AS person_stu_building,
dbo.NAME_MASTER.PREFERRED_NAME AS person_pname,
dbo.NAME_MASTER.MIDDLE_NAME AS person_mname
FROM
dbo.NAME_MASTER LEFT OUTER JOIN
dbo.STUDENT_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.STUDENT_MASTER.ID_NUM
LEFT OUTER JOIN
dbo.ADDRESS_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.ADDRESS_MASTER.ID_NUM
AND
dbo.NAME_MASTER.CURRENT_ADDRESS = dbo.ADDRESS_MASTER.ADDR_CDE
LEFT OUTER JOIN
dbo.TW_WEB_SECURITY ON dbo.NAME_MASTER.ID_NUM =
dbo.TW_WEB_SECURITY.ID_NUM
LEFT OUTER JOIN
dbo.ROOM_ASSIGN ON dbo.STUDENT_MASTER.ID_NUM = dbo.ROOM_ASSIGN.ID_NUM
AND
dbo.ROOM_ASSIGN.SESS_CDE = '2006-FA'
LEFT OUTER JOIN
dbo.BIOGRAPH_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.BIOGRAPH_MASTER.ID_NUM
WHERE
(dbo.NAME_MASTER.ID_NUM <> '') AND (dbo.NAME_MASTER.NAME_FORMAT IS
NULL) OR (dbo.NAME_MASTER.NAME_FORMAT <> 'B')

[Back to original message]