Sample SQL Statements for Downloads

On this page:

Additional SQL statements are needed for defining downloads in the following situations:

Your file includes fields from more than one table. You must indicate how the tables are joined to the table selected in the Table Affected field on the Upload/Download Definition page.
You need to pull more than one value for a student from a record that stores values in multiple records. For example, each district-defined field is stored as an individual record. Therefore, if you need to include data from two district-defined fields, you must define aliases for the fields and define joins and criteria to specify how data should be selected.
You want to define criteria to limit the records processed. If you want to specify criteria but do not need to define a join, you must begin the additional SQL statement with WHERE. For example, to select Registration records for building 2, you would enter WHERE building=2. If you include criteria with joins, do not use WHERE.

This topic assumes that you have experience writing SQL statements to join tables and to specify WHERE clauses. It does not include definitions of INNER JOIN OUTER JOIN or boolean operators.

You can highlight the additional SQL text in the examples below and paste the content into the Additional SQL field for a header. Extra hard returns are added when you paste the content.

Defining Joins for Downloads

To download student information from Registration and include meal status:

Meal status is stored in the reg_personal table. The reg_personal record and reg record can be linked on the student ID to return the meal status for a student. The sample SQL statement that follows joins the two tables and selects active students.

Table Affected: reg

Additional SQL:

INNER JOIN reg_personal ON
reg.student_id = reg_personal.student_id
AND current_status = 'A'

Downloading Registration Information, Including the Student's Home Phone Number

The student's home phone number is stored in the reg_contact_phone table. This table cannot be directly linked to the reg table because it stores a contact ID, not a student ID. The reg_stu_contact table stores both the student ID and the contact ID. The following SQL statement joins reg to reg_stu_contact and reg_stu_contact to reg_contact_phone and selects the student's physical address (P) contact type and the phone number for the home phone (H).

Table Affected: reg

Additional SQL:

INNER JOIN reg_stu_contact ON
reg_stu_contact.student_id = reg_contact.student_id

INNER JOIN reg_contact_phone ON
reg_stu_contact.contact_id =
reg_contact_phone.contact_id

AND reg_stu_contact.contact_type = 'P'
AND reg_contact_phone.phone_type = 'H'

Downloading Staff Data That Does Not Include a Student ID

The Run Download option requires that all interfaces include the student ID column from the reg table. The sample SQL statement that follows can be used to "trick" the Run Download so you can download data that is not related to students. This SQL statement returns only one student.

Table Affected: reg_staff_bldgs

Additional SQL:

NNER JOIN reg ON
reg_staff_bldgs.district = reg.district

AND reg.student_id =
(select top 1 student_id from reg reg2 where reg2.district = reg.district)

The interface header must include reg student ID as the first detail line with a length of 0. If you are defining a download that has a delimiter, you must concatenate this detail line to the next detail line. For example, enter C1, ' ' in the Format field for the student ID column and then enter C1 in the Format field for the next column in the interface. You do not need to concatenate the fields if the download is defined to create a fixed length file.

Defining Joins When You Need to Use an Alias

If you are setting up a download that pulls data from more than one field in a district-defined page, you must assign an alias to each source column. The additional SQL statement must include joins to specify how to pull data from the multiple records.

In the following district-defined page, which is stored as Screen 300, data will be downloaded from the Make, Model, and Year fields:

The following entries will join the data with the student's ID and name:

Field Order	Field ID	Table	Alias	Column	Length
1	StuID	reg		STUDENT ID	10
2	LName	reg		LAST NAME	12
3	FName	reg		FIRST NAME	10
4	Make	reg_user	make	FIELD VALUE	15
5	Model	reg_user	model	FIELD VALUE	15
6	Year	reg_user	year	FIELD VALUE	4

The Additional SQL would be entered as follows:

Table Affected: reg

Additional SQL:

RIGHT OUTER JOIN reg_user as make ON make.district = reg.district AND make.student_id = reg.student_id AND make.screen_number = 300 and make.field_number = 1
RIGHT OUTER JOIN reg_user as model ON
model.district = reg.district AND model.student_id = reg.student_id AND model.screen_number = 300 and model.field_number = 2
RIGHT OUTER JOIN reg_user as year ON
year.district = reg.district AND year.student_id = reg.student_id AND year.screen_number = 300 and year.field_number = 3

This statement will return students who have records for the three district-defined fields. To return all students in reg regardless of whether they have a value for one of the selected district-defined fields, change the RIGHT OUTER JOIN to a LEFT OUTER JOIN.