topic Re: DB Extension joins in SQL in Community Forum

DB Extension joins in SQL

ORCSD-DB — Thu, 07 Apr 2022 19:26:57 GMT

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

Re: DB Extension joins in SQL

mmolloseau — Fri, 08 Apr 2022 11:06:22 GMT

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

Re: DB Extension joins in SQL

ORCSD-DB — Fri, 08 Apr 2022 13:29:18 GMT

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

Re: DB Extension joins in SQL

areynoldsK12 — Fri, 17 Jun 2022 00:52:50 GMT

Thanks for the post 😃

Super helpful