DB Extension joins in SQL

[ORCSD-DB]

Writing a SQL export for some staff data that needs a basic / default SchoolStaff extension table field. I am having difficulty with the join for that table with the schoolstaff table. There does not appear to be a DCID field of any type to join on (like with an advanced / many to one extension), but SQL won't recognize the field in the extension table so I assume I need a table join. I have tried the field reference multiple ways: schoolstaff.u_def_ext_schoolstaff.field, U_def_ext_schoolstaff.field, schoolstaff.field, none of those work ("unrecognized identifier" is the run error). 

This should be simple but my brain fails me today. Thanks. 

SELECT
teachers.last_name,
teachers.first_name,
teachers.teacherloginID,
teachers.email_addr,
teachers.title,
teachers.teachernumber,
teachers.homeschoolid,
schoolstaff.status,
teachers.staffstatus,
schoolstaff.u_def_ext_schoolstaff.oraccounttype  
FROM
teachers
INNER JOIN SCHOOLSTAFF on teachers.dcid=schoolstaff.users_dcid
INNER JOIN u_def_ext_schoolstaff on u_def_ext_schoolstaff.DCID=schoolstaff.dcid

[mmolloseau · Accepted solution]

Hi there,

On u_def_ext_schoolstaff  there should be a SCHOOLSTAFFDCID

Example:

INNER JOIN u_def_ext_schoolstaff on u_def_ext_schoolstaff.SCHOOLSTAFFDCID=schoolstaff.dcid

In the select statement to pull the oraccounttype it would be:  u_def_ext_schoolstaff.oraccounttype

Sincerely,

-Matt Molloseau

[mmolloseau · Accepted solution]

Hi there,

On u_def_ext_schoolstaff  there should be a SCHOOLSTAFFDCID

Example:

INNER JOIN u_def_ext_schoolstaff on u_def_ext_schoolstaff.SCHOOLSTAFFDCID=schoolstaff.dcid

In the select statement to pull the oraccounttype it would be:  u_def_ext_schoolstaff.oraccounttype

Sincerely,

-Matt Molloseau

[ORCSD-DB · Accepted solution]

thank you. I forgot to look in DEM to see the data structure since the DBExt management does not show that field. 

[areynoldsK12]

Hi

Thanks for the post 😃 

Super helpful