1、Query Me This!Configurable Joins Overview,Tips,Tricks,and Processes for Everyone from Novice to MasterChuck WilsonUniversity of ChicagoEnrollment and Student AdvancementQuery me this!Enrollment and Student AdvancementWho is this for?Enrollment and Student Advancement Query Masters(Very familiar with

2、 the data.Builds the difficult queries.)Query Builders(Can build queries but needs help with complexity.)Query Adapters(Altering and adapting existing queries to fit needs.)Query Runners(Go to query,click a button,and say run little query run!)There Are Four(4)PrimaryCategories of Query Users Out th

3、ereEnrollment and Student AdvancementBut Why Configurable Joins?Enrollment and Student AdvancementIt Seems So Much More WorkTo Do The Same Thing!Enrollment and Student AdvancementIt Seems So Much More WorkTo Do The Same Thing!Enrollment and Student AdvancementIt Seems So Much More WorkTo Do The Same

4、 Thing!Enrollment and Student AdvancementIts Not As Complex As It SeemsAnd You Get So Much More!Enrollment and Student AdvancementTrade In Your Old QueriesFor a Faster More Accurate Model!What Do We Get?Enrollment and Student AdvancementLets CompareEnrollment and Student AdvancementLets CompareQuery

5、 BasesQuick and easy if you know the names of the exports and filters you are looking for.Variable speeds depending on SQL construction.Some fields require configuration.Limited quantities of exports for repetitive fields.Requires custom SQL to expand.Reliant upon supplied naming conventions or SQL

6、analysis to know exactly what it is doing.Configurable JoinsQuick and easy if you set up templates and libraries.Streamlined for faster processing.Most fields are already included for use with a quick refresh.Can join as many tables or subqueries as desired.Can better control how you want your expor

7、ts to display with aggregates,concatenates,etc.Queries do exactly what you tell them to do.Enrollment and Student AdvancementConfigurable JoinsPuts the Power in Your Hands!Enrollment and Student AdvancementBut How?!Enrollment and Student AdvancementFor those who possess the predisposition,I can teac

8、h you how to bewitch the queries and ensnare the data.I can tell you how to bottle statistics,brew aggregates,and even put a stopper in elapsed time errors themselves.Enrollment and Student AdvancementUnderstandingCJ Queries How does a CJ Query work?When do we use a Join vs a Subquery?What are all t

9、hose outputs?AdvancedTips&Tricks Nesting subqueries.Independent Table Joins.SupportingUsers Permissions for access.Templates for simulating what we had with Query Bases.Enrollment and Student AdvancementUnderstandingCJ Queries How does a CJ Query work?When do we use a Join vs a Subquery?What are all

10、 those outputs?Enrollment and Student AdvancementBUILDING ANDREFRESHING FIELDSAHEAD1 SLIDEEnrollment and Student AdvancementGetting Your Fields:Refresh Is Your FriendAfter you add any new field,choose the prompt or appropriate data type so the Query Builder knows how to handle it,then refresh CJ Lib

11、rary to get it.Enrollment and Student AdvancementCONFIGURABLE JOINEXPLORERAHEAD1 SLIDEEnrollment and Student AdvancementTHE CONFIGURABLE JOIN EXPLORERFor a quick reference on what tables can join,we can use the Configurable Joins Base Explorer.With a few dropdown options,we can see how any of the ta

12、bles connect to any other table.Enrollment and Student AdvancementCONFIGURABLE JOIN EXPLORERFor example,when we choose the Address base and click the Diagram tab,we can see what tables the address table can join to.Enrollment and Student AdvancementTIME TO DIVE INTOTHE RABBIT HOLELets build a query

13、together and talk through how everything works!Enrollment and Student AdvancementFIRST OFF:WHAT TO CHOOSE?Enrollment and Student AdvancementWHAT TO CHOOSE?What data do we primarily want to get?What we choose here will determine how many rows our query starts with.Enrollment and Student AdvancementRe

14、cords Persons Applications DatasetsRelated Addresses Checklists Devices Materials SchoolsSystem Checklist Options Field Names Material Names Prompt OptionsThese are the primary records in your system.The records that all other data attaches to.This is the data that attaches to the primary records.Fo

15、r example,person records have addresses and application record have checklists attached to them.This is the system settings that you set up in order to record information.When you create a new field,prompt,or material for your records to use,it is a system field that then is applied individually to

16、your records.Enrollment and Student AdvancementYOUR CHOICE DETERMINES YOUR ROWSIf we choose applications,we will have one row per application.PERSON DATAMary PoppinsIndiana JonesJane GoodallAPPLICATION DATAMary Poppins Application#1Mary Poppins Application#2Indiana Jones Application#1Indiana Jones A

17、pplication#2Indiana Jones Application#3Jane Goodall Application#1If we choose person,we will have one row per person.Enrollment and Student AdvancementLETS GET BUILDINGFor this example,we want application data.We would like to have access to each application within our parameters even when someone h

18、as more than one,and we would like each application to be on its own row.So,we will need to start from the application base.Enrollment and Student AdvancementNow we have this big blank Application based Configurable Join query.When thinking of what that means,imagine this like a spreadsheet of every

19、 application in the system and their application scoped fields.Enrollment and Student AdvancementNOW WE HAVE A TABLE OF APPLICATIONSFor example,all these rows are applications belonging to Indiana Jones,we are getting multiple rows of application data for him because he has multiple applications in

20、the system and every application will come through on its own row,because we chose the Configurable Join Base of Application.Enrollment and Student AdvancementDATA DATA DATAAt this point,if I were to click on the Export button or the Filter button,it would give me a list of options from the query ba

21、se that I chose.Enrollment and Student AdvancementEXPORTS AND FILTERSOur exports and filters will automatically have all of our application scoped fields to use.When we are first learning Configurable Joins,lets turn off extended filters until were comfortable using those.Making all of our joins man

22、ually while learning configurable joins will help us understand what the joins are doing and how they work.Enrollment and Student AdvancementEXPORTS AND FILTERSNow with the Extended Exports off,we can see what data we currently have based on the query base we chose.Right now,we only have Application

23、 data because we started with the application base and have not connected any other tables so if we wanted person data,we wont be able to find itbecause we didnt join our current application table to the person table yet.Enrollment and Student AdvancementJOINING DATAWhen getting data from other tabl

24、es,we have two choices.The little three dot symbol(multiple row indicator)next to exports and filters are for building Subqueries,which allow us to filter or export more than one line of data from another table,but only for that one export field or for that one filterit is not available anywhere els

25、e in the query.The two circle Join icon at the bottom is to join an entire table of data to use in both exports and filters(but you get only one row).Enrollment and Student AdvancementJOINING DATAWhichOneDoWeChoose?Enrollment and Student AdvancementIMAGINE A LAKE OF DATAThe type of Joins are ships a

26、nd submarines that move in that data lake.Enrollment and Student AdvancementSTANDARD JOINS ARE BOATSEnrollment and Student AdvancementSTANDARD JOINS ARE BOATSImagine the water as your data.Standard Joins float on top of the water and only allow you to see the top of the water.When you make a standar

27、d Join,you can only walk from boat to boat across the top of the water(one row),you cant see all of the other data beneath it.ONE ROWEnrollment and Student AdvancementSUBQUERY JOINS ARE SUBMARINESEnrollment and Student AdvancementSUBQUERY JOINS ARE SUBMARINESEnrollment and Student AdvancementSUBQUER

28、Y JOINS ARE SUBMARINESSubmarines can go down into the water to see all the data that the boat cant see.Enrollment and Student AdvancementMNEMONICThe mnemonic that I teach people to remember that subqueries see all the data and not just the top row isEnrollment and Student AdvancementMNEMONICThe mnem

29、onic that I teach people to remember that subqueries see all the data and not just the top row isEnrollment and Student AdvancementLETS LOOK AT A JOIN(SHIP)What do we get when we connect a standard Join?Enrollment and Student AdvancementJOINING DATAWhat this button does is join two tables together s

30、o that we can use all of the fields on that table in our exports and filters,but we only get one row of data from that joined table.What does that mean?Well,it means that if the table we are trying to connect to has more than one row of data,we have to choose which one row we want to get.Enrollment

31、and Student AdvancementJOINING DATAFor example,lets imagine a scenario where we chose a Person base.We have the Person table and want some Application table data.A person can have more than one application,but we only have one row for that person.We cant fit all of the other rows of application data

32、 in the one row of person data.PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallAPPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1Enrollment and Student AdvancementSQL CODERSAVERT YOUR EYES!

33、Enrollment and Student AdvancementJOINING DATAAPPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1Enrollment and Student AdvancementJOINING DATAThe configurable join cannot Join these two tables th

34、e way that we see it is now,because there are too many options on the other table and we only have one row in our base to put any other row.It does not know which one row to give us.PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallAPPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Ap

35、plication#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1Enrollment and Student AdvancementJOINING DATASo we need to choose which application should link up using the settings in the join option in order to let the system know which application to bring over.(In thi

36、s case,the Rank 1 App.)PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallAPPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1Enrollment and Student AdvancementJOINING DATANow that weve set the

37、Join to understand which single row out of the multiple options to bring over,it will be able to join those tables.APPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Application#1Jane Goodall Application#1PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallEnrollment and Student Advance

38、mentJOINING DATABecause the table we are joining to now has the exact same number of rows as our query base table.APPLICATION DATA(join)Mary Poppins Application#1Indiana Jones Application#1Jane Goodall Application#1PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallEnrollment and Student Advancem

39、entJOINING DATAAlright,lets push the actual button and see what that looks like.Wont you join together with the base!Enrollment and Student AdvancementJOINNow we have a whole world of tables that we can connect to our application table.Enrollment and Student AdvancementJOINLets take a closer look.Wa

40、it!Whats this?!More icons?!Enrollment and Student AdvancementJOINWhat do these mean?Enrollment and Student AdvancementJOINLets start with the easiest one first.No Icon.It means that this data has only one row that exists on that table that corresponds to your current table,so it is a direct join wit

41、h nothing more to worry about.Enrollment and Student AdvancementJOINThe join automatically knows what row to use in the join,because there is only one row option that matches in the joining table.PERSON DATA(join)Mary PoppinsIndiana JonesJane GoodallAPPLICATION DATA(base)Mary Poppins Application#1In

42、diana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1(A person can have multiple applications,but an application cant be for multiple people.So,there is only one row that the application can connect tothe one that created the application.)Enrollmen

43、t and Student AdvancementPERSON DATA(join)Mary PoppinsIndiana JonesJane GoodallAPPLICATION DATA(base)Mary Poppins Application#1Indiana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1PERSON DATA(base)Mary PoppinsIndiana JonesJane GoodallAPPLICATION

44、DATA(join)Mary Poppins Application#1Indiana Jones Application#1Indiana Jones Application#2Indiana Jones Application#3Jane Goodall Application#1Not enough rows to join all of the rows in the other table(multiple matching).Enough rows to join all of the rows in the other table(only one matching).Enrol

45、lment and Student AdvancementJOINWhat about this wizard hat icon?Enrollment and Student AdvancementJOINThis icon means that there are a lot of options when joining to this table and that Technolutions has supplied us with a wizard to help configure exactly which row from which table we might want.En

46、rollment and Student AdvancementJOINFor example,in this casewe might want to join to a very specific form instead of the entire form response table.That would allow us to bring specific data found only in that form in order to use as exports and filters in our query.Enrollment and Student Advancemen

47、tJOINThe multiple row indicator is back!This is a warning that the data we are connecting to has more than one row that could match our current base.In this case,our base is the application,and an application can have more than one material connected to it.Enrollment and Student AdvancementJOINRemem

48、ber!We dont get more than one row!PERSON DATAMary Poppins App#1Indiana Jones App#1Jane Goodall App#1APPLICATION DATAMary Poppins Application#1 ResumeIndiana Jones Application#1 ResumeIndiana Jones Application#1 StatementIndiana Jones Application#1 SupplementJane Goodall Application#1 ResumeEnrollmen

49、t and Student AdvancementJOINWhen we click on a join with this icon,we will be presented with a set of options to pick the one row from that table that we wish to use in our exports and filters.Enrollment and Student AdvancementJOINWe can do this via choosing specific filters(for example,we want onl

50、y the“resume”material type).But what if we have more than one“resume”,then we could sort by something such as the created date.Now that we have that table filtered and sorted,we choose if you want the first row,second row,third row,etc.by choosing the offset.Enrollment and Student AdvancementJOINWev

51、e isolated this table to only give me the resume materials.Were sorting the table by the created date descending so that the most recent material is at the top.And weve set the offset to grab the very first row it finds,which would be the most recently added resume material.Enrollment and Student Ad

52、vancementJOINAPP DATA(base)Mary Poppins App#1Indiana Jones App#1Jane Goodall App#1MATERIAL DATA(Join)Mary Poppins Application#1 ResumeIndiana Jones Application#1 ResumeJane Goodall Application#1 ResumeNow we have created is a join to a table that brings over the one row in the materials table that w

53、ould correspond with the most recent resume material.Enrollment and Student AdvancementJOINNow lets save it off and see what we have.Enrollment and Student AdvancementYAY!Enrollment and Student AdvancementJOINWaitThat Join is just labeled as“Materials”which isnt quite right.Its a Join to the materia

54、l table,but specifically only the most recent resume material in the material table.Enrollment and Student AdvancementLets give it a name that makes sense to us and anyone else that would use this query.Enrollment and Student AdvancementNow we have a nice Join,giving us access to the material table

55、to get to the exact data that we want with a clear name that tells us what it is doing.Enrollment and Student AdvancementIf we click the export button,we have a set of exports from the most recent resume material on the application.Enrollment and Student AdvancementIf we click the filter button,we h

56、ave a set of filters that we can use to isolate our appliation data based on parameters from the most recent resume material.Enrollment and Student AdvancementRINSE AND REPEATEnrollment and Student AdvancementLets try a different table.Some tables have a built-in field called“Rank”that can be used t

57、o automatically choose the table row for you without having to use filters and sorting.For example,an application can have more than one decision,but by using the table with the rank option,we can choose the single ranked row.That is why they do not have the multiple row indicator.THE POWER OF RANKE

58、nrollment and Student AdvancementTHE POWER OF RANKBy choosing the Decision by Rank and setting it to Rank 1,it will automatically select the most recently created decision.Setting it to Rank 2 would return the second most recent decision created.Decision by Rank Confirmed of Rank 1 would return the

59、most recent confirmed decision.Decision by Rank Released of Rank 1 would return the most recent released decision.Enrollment and Student AdvancementTHE POWER OF RANKDont forget to rename the table so that we and everyone else knows what it is doing.It makes the table Joins easy to understand at-a-gl

60、ance and helps when choosing filters and exports for your query.Enrollment and Student AdvancementLETS ADD AN ADDRESSEnrollment and Student AdvancementMISSING TABLE?What happens when we cant find the table were looking for?In this example,we want the address.The reason we cant find it is because add

61、resses are not connected to application data.They are connected to the person.So,to go from the application to the address,we first need to Join to the person table.If ever we cannot find a field or table,it is very likely because there is another table we need to connect to first.Enrollment and Stu

62、dent AdvancementRemember when we looked at the Address table in Configurable Join Base Explorer?It does not show a connection to the application,but it does show a connection to the person.So we need to get there first.Enrollment and Student AdvancementJOINING UP THE TABLESSo based on that knowledge

63、,if we Join to the person table first then we should see the ability to find the address table on our next Join.And now we have access to the address table with a few different choices.Enrollment and Student AdvancementADDRESS OPTIONSWe can get the overall rank address,which is the most recent highe

64、st priority,highest quality address on the record sorted by mailing address first and if mailing address doesnt exist then permanent address.Or we can get the ranked address by type,which is the most recent highest priority,highest quality address of a certain type(mailing or permanent)on the record

65、.Enrollment and Student AdvancementSCHOOL OPTIONSHeres a fun one for all of you who like lots of school options or need to export lots of school information in a feed.By joining the Person table to the School by Level of Study,Rank table.Enrollment and Student AdvancementSCHOOL OPTIONSWe can grab th

66、e Rank 1 Undergraduate School,which is the most recent conferred or date attended school in that level.Renaming the Join.Then copy that join.With a quick rename and change of the ranking to 2,we can pull the second most recent school.Enrollment and Student AdvancementSCHOOL OPTIONSRinse and repeat t

67、o get as many of the most recent schools as you would like,then all of the export fields and filters for each school will be available under the name of that join.Enrollment and Student AdvancementWHAT ABOUT MORE DATA?Weve connected our query to a table of the Rank 1 Decision but what if we wanted t

68、o see or filter on all of the decisions?We cant do that from our current Join because that Join table is only looking at the Rank 1 decision.Enrollment and Student AdvancementSUBQUERIESEnrollment and Student AdvancementSUBQUERIESSubquery exports allow us to combine multiple values from other tables

69、into the same field.Subquery filters allow us to look at all the different data rows in a table to set parameters for our filters.Enrollment and Student AdvancementSUBQUERY EXPORTLets say we want to look at all of our decisions for an application.I can use a subquery export to create a field that wi

70、ll combine a list of all of those decisions separated by a chosen delimiter.To build a subquery export,click on the multiple row indicator button next to exports.Enrollment and Student AdvancementSUBQUERY EXPORTOnce we begin a subquery export,we will find some familiar options here similar to the sa

71、me joins and exports that the regular query has.Being a subquery export though,these all now work slightly different.We are now assembling all of this data into a single field to display in the main query.Enrollment and Student AdvancementSUBQUERY EXPORTTo see all of our decisions in this subquery e

72、xport field,we are once again going to need to Join to the decision table.Enrollment and Student AdvancementSUBQUERY EXPORTWe want to Join from our application base to the Decisions table just like we did originally,but this time we want all the decisions.Enrollment and Student AdvancementSUBQUERY E

73、XPORTNotice there are no options that we need to choose here in order to isolate the table to a certain row.Thats the power of Subqueriesthey can access ALL of the rows.I suggest renaming the Subquery Join to be called“Subquery”somewhere in the name to be able to tell the difference between that and

74、 any standard join you may have.Enrollment and Student AdvancementSubquery Join that automatically joins to all the rows in a table.Standard Join that requires you to choose one row within the table to connect to.Note:anytime you see a“Row Offset”it means you are only getting one row.Enrollment and

75、Student AdvancementSUBQUERY EXPORTNow that we have our subquery joined to the appropriate table in order to retrieve all our decisions.We need to decide how we want those decisions to display,and we need to decide if there are any filters to apply to get only certain decisions(released only,confirme

76、d only,etc.)and if we want them to come out in any particular order.Enrollment and Student AdvancementSUBQUERY EXPORTFirst,well name the export what we want the query field to be labeled.Then we will change the output to Concatenate(combine)our export fields together.We will choose what Row Separato

77、r we would like(I choose because I like the way it reads).And then we will choose what information to combine.In this case,the Decision Name.Then well set a sort by created date so that we can see them displayed in the order they were created.Enrollment and Student AdvancementNow with our first subq

78、uery export built,we can click the Preview Results to see a list of all the decisions in created date order separated by and displayed in that“All Decisions”field that we created.Of course,other filters can be added within that subquery export if we only wanted certain decisions,or only released dec

79、isions or any other criteria that we would like to use to isolate those decisions.But it wont prevent those records from being in the query,only form exporting those values in this field.Enrollment and Student AdvancementWhat if we wanted something different than just a concatenation(combination)of

80、all the existing values.What if I wanted just to know how many there are?Enrollment and Student AdvancementWhat if we wanted something different than just a concatenation(combination)of all the existing values.What if I wanted just to know how many there are?Enrollment and Student AdvancementLets us

81、e the handy dandy copy icon on the Subquery that we have already created so that we can reuse the same details except this time,lets get some aggregate data,in this particular case,the count.SUBQUERY EXPORTEnrollment and Student AdvancementSUBQUERY EXPORTFirst Ill rename the export what I want the q

82、uery field to be labeled.Then I will change the output to Aggregate and choose the Count option.There are lots of other neat aggregate functions in here too,but right now we just want a count.So now this subquery will return a count of all the decisions identified in the subquery filters.Which in th

83、is case is no filters,so it will return a count of all decisions.Enrollment and Student AdvancementSUBQUERY EXPORTNow when I run a preview,Ill see that I have all of the decisions being displayed separated by a symbol in the All Decisions field.And a count of all those decisions in the Decision Coun

84、t field.Enrollment and Student AdvancementWHAT ABOUT THE OTHER OPTIONS?Rank:Works like the rank on the table,returning the most recent entry.Coalesce:Returns the first non-null value.Aggregate:Numeric aggregates like counts,averages,and sums.Dictionary:Assembles values for use in liquid markup.Exist

85、ence:Returns a designated value if a field value exists or not.Split:Separates values by a delimiter and returns the one specified.Formula:Get fancy with SQL.XML and JSON:Mostly used when needed in exports and data manipulation.Enrollment and Student AdvancementPLAY WITH THE OPTIONSEnrollment and St

86、udent AdvancementWHAT ABOUT SUBQUERY FILTERS?But what if we only wanted a record that had a specific decision somewhere on their application?Even if it was the most recent decision,our correspondence decisions are not ranked and so we would never get it with our rank 1 decision table Join.Subqueries

87、 to the rescue again!But this time in the filters section.Enrollment and Student AdvancementSUBQUERY FILTEROnce you begin a subquery filter,you will once again find some familiar options here similar to the same joins and exports as the regular query.Being a subquery though,these all now work slight

88、ly differently.We are now able to use this filter to look through all rows of a table.Enrollment and Student AdvancementSUBQUERY FILTERTo have the filter look through all of our decisions in this subquery filter in order to find the ones that we want,we are going to once again Join to the decision t

89、able.Enrollment and Student AdvancementSUBQUERY FILTERWe want to Join from our application base to the Decisions table just like we did in the subquery export and for the same reason,because we want to use a filter that looks at all the decisions on the application.Enrollment and Student Advancement

90、SUBQUERY FILTERWe should rename this join so that we can identify it for this subquery so that it does not get confused with other joins.(If we just left every decision join in the query named“Decisions”it could get confusing.)No offset means we are seeing all of the decisions.Enrollment and Student

91、 AdvancementSUBQUERY FILTERWe should rename this join so that we can identify it for this subquery so that it does not get confused with other joins.(If we just left every decision join in the query named“Decisions”it could get confusing.)Enrollment and Student AdvancementSUBQUERY FILTERNow,when we

92、choose the Correspondence decision,were looking to see if there are any Correspondence decisions on the application.Enrollment and Student AdvancementSUBQUERY FILTERChoosing the Aggregate“Exists”option will pull every application with Correspondence decision anywhere on the application.If we changed

93、 that to“Not Exists”then it would pull every application that did not have a Correspondence decision anywhere on the application.Enrollment and Student AdvancementSUBQUERY FILTERSince we chose that we want applications with Correspondence decisions that exists,well see that my query is now isolated

94、to only pull applications that have a Correspondence decision somewhere on the application.Enrollment and Student AdvancementAdvanced Tips&Tricks Nesting Subqueries.Independent Table Joins.Enrollment and Student AdvancementNesting SubqueriesLets look at reasons we may need to nest subqueries into su

95、bqueriesLike,lookout,man!Math!Enrollment and Student AdvancementNesting SubqueriesWe all know that getting the maximum value for my verified uncancelled tests is as easy as aggregating the Max of the Score with some filters.Enrollment and Student AdvancementNesting SubqueriesBut what if I need the h

96、ighest Math section plus the highest EBRW section for a super scored value?Enrollment and Student AdvancementNesting SubqueriesNow we need to add the two highest values,which are subqueries just like we built before,but this time nested inside our subquery so that we can use them in the formula.Enro

97、llment and Student AdvancementNesting SubqueriesAnd voila!Our two amounts are smooshed together?Enrollment and Student AdvancementNesting SubqueriesWelcome to SQL Math!Where a+is a concatenate if the two values are not numbers.So this means the query isnt seeing them as a number,so a quick convert w

98、ill fix that.Enrollment and Student AdvancementNesting SubqueriesThat looks more like it!Enrollment and Student AdvancementINDEPENDENTTABLE JOINSAHEAD1 SLIDEEnrollment and Student AdvancementIndependent Table JoinsWhy would we need to join an independent table?Well,lets come up with a scenario.Lets

99、imagine for a moment that we have a field called Country of Birth,which captures the country name,but in our feed we need the Alpha 2 export.Enrollment and Student AdvancementIndependent Table JoinsOne option might be to create a translation table with all the country names and Alpha 2 values and ex

100、port that export value.Enrollment and Student AdvancementIndependent Table JoinsWhat if there was a way to use the already existing Country table that Slate already has in the system?Slate doesnt know to connect to it from that field but we can tell it to do it.Enrollment and Student AdvancementInde

101、pendent Table JoinsBy creating a new subquery and pulling in the System World Country table,we can set it to compare two values and give us the resulting field.Enrollment and Student AdvancementIndependent Table JoinsThis pulls in the entire built in Slate country table for us to use,all we have to

102、do is tell the subquery which value we want returned.Enrollment and Student AdvancementIndependent Table JoinsFor this,we use another subquery filter and choose the Comparison aggregate feature.Then we export the Person Country of Birth and compare it to the World-Country Name.Enrollment and Student

103、 AdvancementIndependent Table JoinsThen,we export our desired field.in this case,when the Country of Birth Value=The World-Country Name,then export the Alpha-2 value from the World-Country table that corresponds to that.Enrollment and Student AdvancementIndependent Table JoinsSo now,when the Country

104、 of Birth Value is equal to a name on the World Country table,then it will return the Alpha-2 value from the table which corresponds to that name.Enrollment and Student AdvancementSupporting Users Permissions for access.Templates for simulating what we had with Query Bases.Enrollment and Student Adv

105、ancementSupporting the UsersThe query users dont have to know how to do all the things that weve just gone over.They can,but if they dont we can still support them with very robust query building.Enrollment and Student AdvancementPERMISSIONSAHEAD1 SLIDEEnrollment and Student AdvancementPermissionsFi

106、rst off,lets look at the user permissions of Configurable Joins that you can assign to a user or role.By clicking the expand button,you can see how granular you can get.Enrollment and Student AdvancementPermissionsTheres a lot of themAh Ah Ah!Enrollment and Student AdvancementQuery Library/Template

107、PermissionsBeyond the super granular user permissions,the Query Library and Templates both also have permissions.Enrollment and Student AdvancementQUERY LIBRARYAND TEMPLATESAHEAD1 SLIDEEnrollment and Student AdvancementQuery LibraryThe Query Library is a zone where you can build out the more time co

108、nsuming exports and filters into a query that gives quick access to any other query that you build with that table base.Enrollment and Student AdvancementQuery LibraryNow those library exports and filters are there for me to add to any of my queries without having to rebuild them.Enrollment and Stud

109、ent AdvancementQUERYTEMPLATESAHEAD1 SLIDEEnrollment and Student AdvancementQuery TemplatesBy starting with a Template type,the users get to pull in a query that you have already constructed for them.These Template Queries can be isolated behind permissions to allow only certain roles and users to se

110、e the Templates that they would use.Enrollment and Student AdvancementQuery TemplatesThe Template Queries come complete with all of the joins,filters,and exports that you had built in the Template.Meaning,most of your users will never need to make another join themselves.I usually include various fi

111、lters and exports that the users of that template need,and even some that I leave in the query but inactive to remind users to keep certain fields and parameters in mind even if they dont need them at that particular time.Enrollment and Student AdvancementQuery TemplatesBy adding exports and filters

112、 without the Extended checkbox,users will never stray out of your already established query eco system and they will always have the accurate information from the tables and structure that you have already set up.If a user is ever missing a join or subquery that they would need,they can just reach o

113、ut to the Query Masters to add that to their template so that they have that going forward.Enrollment and Student AdvancementQuery TemplatesYou could have many different templates in the system tucked behind roles so that each person(except administrators)would only see the few templates that they w

114、ould use on a regular bases completely customized to their needs.Enrollment and Student Advancement Query Masters(Building queries and templates from scratch.)Query Builders(Building queries from templates but comfortable adding additional joins and subqueries as necessary.)Query Adapters(Building q

115、ueries from established templates as needed.)Query Runners(Hopefully interested in ranking up to Query Adapters now?)Looking Back at the Four(4)PrimaryCategories of Query Users Out thereEnrollment and Student AdvancementQUERY LIKE A HERO!Now using the power of Joins,subqueries,libraries,and template

116、sthe data world has become your oyster As long as you become comfortable with the structure.Enrollment and Student AdvancementWRAP UPThere are SO many more things that CJ Queries can do,but the only way to discover them it to take what we learned here and keep playing!Enrollment and Student Advancem

117、entAssess how many rows you want to see for your data source(1 per person,1 per application,1 per field,etc.)then choose the appropriate query base.UNCHECK“Extended Filters”so that you know you are only working with the Joins that you have established.Use the ships and submarines idea to choose whet

118、her you use the standard Join or a subquery.Use nested queries and formulas to get at more complex data.User Libraries for quick assembly of the more complex exports and filters.Use Templates to give your users an experience similar to the old Query Bases,but with far more control by your Query Masters.RECAPEnrollment and Student AdvancementQUESTIONS?COMMENTS?THREATS?NOW THAT WEREThank YouEnrollment and Student AdvancementHappy Querying,Happy Querying,-Chuck-Chuck#SlateSummit