DB Extension joins in SQL

ORCSD-DB
Practitioner

DB Extension joins in SQL

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

 

 

2 Solutions
mmolloseau
Practitioner

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

View solution in original post

ORCSD-DB
Practitioner

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

View solution in original post

3 Replies
mmolloseau
Practitioner

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
Practitioner

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

areynoldsK12
Hobbyist

Hi

Thanks for the post 😃 

 

Super helpful