Distinct intersection query
|Top Previous Next|
The Distinct Intersection query is used to collect all of the distinct intersections from with in an entire database. Since most systems do not already have a table of intersections like this, we query the crashes database for the distinct primary and cross street values. In this case, it is also important to include a UNION statement to assure that the list includes both possible intersection names: Primary & Cross as well as Cross & Primary. Note the highlighted section where the street names are reversed. Changing the alias names does not accomplish this "flip flop".
As of this manual printing, the only way to accomplish a UNION such as this is to directly edit the XML for the Query:
1.Make sure there is no content between the <UnionQueries>. If there is, remove it.
2.Copy the entire content of the primary <QueryDefinition>
3.Paste the content inside the <UnionQueries> tags of the primary QueryDefinition. This is the "Second Query Definition"
4.In the Second Query Definition, set the <UnionType> to UNION.
5.In the Second Query Definition, Identify the <JoinPair> that joins the primary and cross streets. Reverse them as shown in the examples.