Intro to SQL with Deltek Vision Results from multiple tables
This is a follow-up to the first entry about the introduction to SQL with Vision.
To begin working with multiple tables start by using the Design Query in Editor
And add the EM and PR table.
You can also add a table to an existing query by right clicking in the window and choosing Add Table.
Start by selecting the project tables Name and WBS1 field and then drag the ProjMgr column from the PR table to the Employee field in the EM Table.
That should create the link between the two tables, that specifies ProjMgr (project manager) field is linked to the EM tables Employee (employee field). This is done in the sql window on the bottom by the use of the inner join, which you can see below. The inner join must follow the first From (ie. FROM PR…) and the context first specifies the table to join or link, so in this example Inner Join EM to join the EM table, then the context specifies how to join the tables using the ON keyword which specifies on what relationship to join it, in this example we’re specifying the relations between the Projects Project Manager identity and the EM tables Employee identity, so Inner Join EM ON Pr.ProjMgr=EM.Employee.
So in this example we’re going to get the Projects Number, Name and the Project Managers first and last name. One other important thing to note, notice the select list columns are now preceded with their table names, so the Project Name is now Select PR.Name, whereas before when selecting from a single table only Select Name was necessary. This is to avoid conflicts if there is a column in each table with the same name, so by specifying table name first then .Column name the conflict is avoided. It’s still possible to do a select from two tables without using the table name in the select, you’ll just get an error if there is a conflict.
Note if when dragging the ProjMgr over to the EM table you may just miss the Employee field in the EM table and have a different result in the sql window, that’s fine just manually change it to read ON Pr.ProjMgr=EM.Employee.
You can optionally use the Query Designer to filter results to just say just get the projects for a single pm by adding the pm’s name to the filter fields (again note the sql where is updated for you).
You can now view the results by clicking OK and pressing execute (or f5 on the keyboard).
More tables can be added using multiple joins, to begin simply open the Query Designer and add the EM, PR, and Organizational table. Note this time the Organization table was connected to the PR and EM table for you automatically, that’s because a relationship (called a foreign key relationship) was already specified in the table. If the join wasn’t created automatically, you could simply drag the Em’s Org field to the Organization org field. For this example just remove the PR join by right clicking on the link between the PR and Organization table and click Remove.
Now just drag the ProjMgr to the Employee field again to join PR table to Em table. As you can see below the sql join order is a little different but it has no impact on the results since we’re using inner joins, this reads as “select the Projects wbs1, Projects name, employee’s last name, employee’s first name, and Organization org from the employee table, join the Organization table on the Employee’s Org equal to the Organzation’s org and join the PR table on the Employee id equal to the Projects Projmgr”. Now just choose the columns the report on.
Running the SQL in this example will show the Project, the Project Manager, and the Organization- note in this example it’s the Employees organization.
Changing the join on the PR org to the Organization org field will change the results to show the projects organization instead of the Project Managers organization. We can use the where statement filter out the results to just show active projects in the BO:AR org, by using the Where statement.
This where statement can also be done for you by using the Filter column in the Query Designer. Another common task is to sort the result data, so if you wanted to sort a Project Managers projects by Compensation (Fee in the Project table), you can choose the Sort Type from the drop down and choose either Ascending or Descending order. This will create an Order By statement for you in the SQL, to use order by simply list the columns you’d like to sort by (Note they do not need to be in your select list, but they usually are). To sort by multiple columns simply separate each entry with a coma, so you could sort by Organization and then PM and then Fee, by default sorts or done in ascending order but if you would like the order by done in descending order you must use the key word DESC after the column.
You can see the results below sort the results by Fee is descending order. Most likely you would want to filter this result to just Project level results and not have the lower level phases (wbs2) and tasks (wbs3). You can do that by just adding Wbs2=” in the Where clause, so it would read Where Organization.Org = ‘BO:AR’ and PR.WBS2=”.
Another good way to experiment and learn about what’s in the tables you’re reporting on is to use the * at the end of the Select statement to get all the columns from all the tables and then view the results, once you find the item or result you’re looking for in the columns simply add it to the select statement.
If you only want to retrieve all the columns from ONE table you can precede the * with the table name, so in this example it would be PR.* to retrieve all the Project columns.
You can come across cases where you will reference a table more than once because of different relationships that may exist between the tables, for example you may include relationship and join between an employee table the project table’s PM, as we’ve shown before, and you may relate the em table again on relationship between that em table the project’s principal. Note that these will need to be joined as different table instances because the PM and the Principal are separate relationships (if you were to run it on a single join you’d only get projects where the pm and principal are the same employee). To create multiple joins on the same table you simple use the an AS to relabel the table, in the using the query designer it will do that for you by adding an AS then the table name and _# for additional duplicate tables, which you can see below in the AS EM_1 and AS Organization_1.