Archive for Application Development

T-SQL Inner Join Need Not Always Be an Equal Join

Have you ever had to write a T-SQL query that joined a table to itself? The classic example is an employee table that lists the ID of the person’s supervisor or boss, which refers to another row in the same table. This example is nothing like that (Hint: If you do have to do that, do a search for “recursive hierarchy”). Our example today is something different.

In our example we’re going off of the following scenario: given a list of cities on a map, generate a table that can record the distance between any two. In other words, create a list where every value in the table is paired with every other value. For this exercise, we’re not going to be calculating the actual distance, simply creating the records and leaving the space for someone else to fill in. Sounds simple enough. Let’s start with the simplest example: TWO points: New York and San Francisco:

We could easily create a simple three column table with the following entries:

But the distance in one direction is the same as the return trip so really, we only need one row. After deleting the redundant record and adding Chicago to our list of cities, we get this:

 

As you can see, with the addition of just one value to our list of cities, we need two additional rows in the Distances table. As you might expect, the fourth city in the first table would require three more rows in the second. The fifth city would require four more and so on. The growth is almost exponential. At 100 cities, we would need 4950 pairs of cities in the distance table, and at 1,000 we would need close to half a million. The actual number is

{ N * ( N – 1 ) / 2 }.

Time to automate, which is the focus of this article.

Let’s settle on a total of five cities, with the last two being New Orleans and Las Vegas. We could easily build the grid below and fill in any of the appropriate blocks. The black squares are same-city pairs (New York to New York, or Chicago to Chicago, etc.) so we don’t need to worry about them. And if we can get the distance from New York to San Francisco, for example, we don’t need the distance from San Francisco back to New York, so we can also eliminate all the squares marked in gray:

Let’s replace our city names with numbers. After all, any decent database design will have a unique key assigned to each row of the City table.

And our grid now looks like this:

 

So how do we create this mega list of relationships from ONE table? An INNER JOIN of the City table to itself on the City ID column will generate the squares marked in black. But that is exactly what we DON’T want. What about a CROSS JOIN?

That will do it, but with over twice the results we need. Remember that once we know the distance from, say, Chicago to Las Vegas, we don’t also need to record the distance from Las Vegas to Chicago. And we’re still left with some extra rows where the cities are the same, like the distance from New Orleans to New Orleans.

Comparing the City ID columns in the result set above with the City ID axis of the grid, we can see that we’re only looking for pairs (cells) where the ID of City 2 is greater than the ID of City 1. From here, we have two choices: Stick with the CROSS JOIN and add a WHERE clause, or go back to the INNER JOIN and change the ON clause:

The results are the same. In fact, the estimated execution plan from the query optimizer is the same as well.

The point of this whole exercise is this: An INNER JOIN need not always be an EQUAL JOIN, though that is by far the most common occurrence. When faced with unique query needs, think outside the box. In this case, simple substituting a GREATER THAN for the EQUAL in the ON clause did the trick.

Learn more about how BlumShapiro Consulting can help with a project like this and additional application development.

About Todd: Todd Chittenden started his programming and reporting career with industrial maintenance applications in the late 1990’s. When SQL Server 2005 was introduced, he quickly became certified in Microsoft’s latest RDBMS technology and has added certifications over the years. He currently holds an MCSE in Business Intelligence . He has applied his knowledge of relational databases, data warehouses, business intelligence and analytics to a variety of projects for BlumShapiro since 2011. 

What to do When There’s No App For That

It finally happened. You have a great idea to streamline a business process or improve customer engagement when you discover that there’s NO app for that. What do you do? How do you build it? The answer is, it depends. It depends on the technical requirements, the target audience, your budget and the platforms and devices you want to target.

Traditionally for modern apps there are 3 approaches

 

Native Apps

A Native app is a mobile application developed in a programming language such as C# for Windows, Java for Android or Objective C for IOS to target a specific device. There are frameworks and tools like Xamarin that allow you to develop native apps with a single codebase in a single programming language targeting multiple platforms, but such tools are not required to build a native app.

HTML5 Apps

HTML5 apps are applications delivered from the web that look and feel like native mobile applications. They run in the browser, and can be accessed like any other web page (open browser, type in the URL, etc.). A responsive website is an example of an HTML5 app.

Hybrid Apps

As the name implies hybrid apps are part native app, part HTML5 app. Hybrid apps can be delivered via an app store and are stored on the device much like native apps. However, unlike native apps hybrid apps are served up through a browser (more specifically a browser control in the application) and are developed using web technologies like HTML5 and JavaScript.

Recently a new type of app has entered the mobile ecosystem. These apps can be developed without writing any code, and can be made available to users within your organization. As such, I’ll refer to these apps as Organization Apps.

Organization Apps

Organization apps are internal line-of-business applications published to users within your organization. Apps developed with PowerApps  from Microsoft are a great example of this type of app. With PowerApps users within an organization can connect to business systems like SharePoint, OneDrive and MS Dynamics CRM to create powerful web and mobile applications which can be made available to other users within the organization.

Another great example is Composer 2 from AppGyver which allows users to connect to business systems like Oracle and Salesforce to create applications for their enterprise.

With so many options for developing mobile applications it can be tough to decide which approach to take. Here is a simple chart covering just some of the many things that should be considered when making the decision on the development approach.

  Native App HTML5 App Hybrid App Organization App
Cost High Moderate Moderate Low
Connectivity Online/Offline Mainly Online Online/Offline Mainly Online
Distribution App Store Web App Store Internal to Organization
Device Access Yes No Yes No
Development Time High Moderate Moderate Low
Developer Skills C#/Java/C HTML/CSS/JavaScript HTML/CSS/ JavaScript None
Cross Platform No Yes Yes Yes


How We Can Help

Fully understanding the requirements for the app and how it will be used will be critical to the apps success. Understanding the security and accessibility of the app is also crucial. At BlumShapiro we have the expertise to bring your app idea to fruition. Talk to us about your app idea to get started.

About Matt:

As a senior in BlumShapiro’s Technology Consulting Group, Matt has over 7 years of experience with Microsoft .NET software application development, including solutions for web, client/server and mobile platforms.

What to do CTA