Geeks on Campus

I’ve been meaning to write a blog post about unions in PS query for some time now, but I didn’t have a good example to work with until today.  So the scenario is that you want to list out all of the items from record (table) A and the count of their occurrences in record B.  As long as each entry in record A has at least one match in record B that’s fine.  You would just use a standard join and put the COUNT aggregate on a field in record B.  But what to do when there are no matching rows in record B and you don’t want to lose any items from A?

The first idea is to just use a left outer join from A to B and count a field in B.  That works mostly fine except you will get a count of 1 for items that have no rows in record B.  To mitigate the confusion this would cause you can output an additional field from the B record.  That way the additional field from B will only display with the items that have at least one row in B and the others with a count of 1 you will know are really 0.  Given the example of counting the occurrences in STDNT_AWARDS (B) of the item types in ITEM_TYPE_FA (A):

Item    Descr          Count   Year
1234   Pell Grant   1,234   2014
2345  ABC Grant            1
3456  Fed Loan     2,345  2014

You can see that no details from the A  record are lost and the “ABC Grant” has no matching B rows because the year field is blank.

Or you could use a Union

I think a cleaner solution would be to use a Union.  You would create the query first using a standard join and count.  That would result in output such as:

Item    Descr          Count
1234   Pell Grant   1,234
3456  Fed Loan     2,345

Then you would add a Union to the query.  This is done using the “New Union” link at the bottom of the Query Manager page (left of the Return to Search button).  It will look like a new query has been started, but it will say “Working on selection Union 1” at the top.  Select your A record and choose the same fields in the same order as the original query.  * An important point is that the union must have the same number of fields of the same type and in the same order as the main query or you will get an error *  Then create an Expression with a type of number and in the Expression Text box just enter the number 0.  Use this Expression as a field.  Finally add a Does not Exist subquery to the union to only select A rows that have no match in the B record.  Then your output will look like this:

Item    Descr          Count
1234   Pell Grant   1,234
2345  ABC Grant            0
3456  Fed Loan     2,345

*Note that the query will use the field names and sorting specified in the Top Level Query.

That should do it.  Hopefully knowing how to use Unions in PS Query will help you write better queries.

*Update* Do note that adding a Union to your query has the effect of checking the “Distinct” box, you won’t get duplicate output.