A SQL case: The INTERSECT statement

I decided to explore another SQL case that also is inspired by Facebook. When you visit a friends profile you can see what friends you and he/she have in common.

 

I have extended the database from my previous post to include a FriendRelations table with the following columns and relations to the Persons table;

As in the previous post this is a many-to-many relationship, but it’s a bit special since the foreign keys of the relation table points to the same primary key. This means that a person can be friend with a person in two different ways, either via the PersonId1 column or the PersonId2 column. Let’s have a look at the test data before we continue;

(In my previous post i accidentally named two persons “Spiderman”, one of them is now changed to “Superman”)

So, for example, the first row to the right means that “Spiderman” and “Superman” are friends with each other. Another example is that “Superman” is friends with “Spiderman”, “Catwoman” and “Tintin”.

In this case there will be two given PersonIds, one that represents the currently logged in user (this might come from a session variable) and one that represents the person profile the user is currently visiting (this might come from a querystring variable). In the following SQL commands these IDs will be hardcoded, I will use 4 (“Spiderman”) as the logged in user and 1 (“Superman”) as the ID of the visited profile. If you study the data above you can see that both these persons are friends with “Catwoman”, so this is the final result set we want from our SQL question;

PersonId	PersonName
5	Catwoman

Let’s start with getting all the friends that “Superman” (PersonId = 1) has;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 
WHERE
    fr.PersonId2 = 1

This will give the following result set;

But wait a minute, “Superman” is also friends with “Tintin”, where did he go? In the above SQL we only get friends where “Superman” is connected through the PersonId2 column of the FriendRelations table. We need to work some more SQL magic to include friends that are connected through PersonId1 as well. Let’s give it a shot;

 SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    fr.PersonId1 = 1 OR fr.PersonId2 = 1

So we add some OR statements to the JOIN and the WHERE, but this resultset still looks a bit odd. It does include “Tintin” but “Superman” isn’t friends with himself and he certainly isn’t friends with himself three times over! However there’s nothing strange with this result, since the SQL above will give us all rows that has any connection with PersonId 1 in the FriendRelations table. So, let’s just filter “Superman” out!

 

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1 
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)

And now we get all the persons that are friends with “Superman” and nothing else;

Friends with “Superman”

And if we run the very same SQL statement but exchange the 1 for a 4, we get all persons that are friends with “Spiderman”;

Friends with “Spiderman”

As you can see if you compare these result sets, the only friend they have in common is “Catwoman”. To get only that row in the result set we can separate both our SELECT statements (the one with a 1 and the one with a 4) with INTERSECT and only get those rows that both of the SELECT statements have in common;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)
    
INTERSECT

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 4 
    AND (fr.PersonId1 = 4 OR fr.PersonId2 = 4)

And finally we get the expected resultset;

Download the database here, this database also includes the final SQL command as a stored procedure.

Note, for an INTERSECT to work, the two SELECT statements has to have identical column lists, at least when it comes to type. In this case it is PersonId (int) and PersonName (varchar).

A SQL case: Relating groups based on membership relations

The other day when I visited a group page on Facebook and looked at the "Related groups" section I started to think about how that SQL question might be implemented.

 
Screen capture from the MSDN Sweden Facebook group.

This is not a list that the group owner can control, instead the list automatically shows groups that has a lot of members in common with the group you're currently visiting.

Without any knowledge of Facebooks design for this, let's assume that the database structure is like this, a very ordinary many-to-many relationship:

To have some test data I filled these tables with the following data.

As you can see the "Comic Book Characters" and the "Superheroes" groups have a lot of members in common while the "Celebrities" group is an island.

So, based on a given GroupId, how can we get a resultset that lists all of the other groups with a column that specifies the amount of shared members? Let's build this step by step, in fact I do this as I'm writing so the final solution is not totally clear to me yet, even though I have some idea. If you want to follow along and run these SQL commands you can download the database file here (this database also includes the final SQL command as a stored procedure).

Let's say that the given GroupId is 2 (so this might come from a querystring or some other input, in these commands the number 2 will be hard coded). This means that the final result we want is this:

GroupId	GroupName	SharedMemberCount
1	Comic Book Characters	4
3	Celebrities	0

Let's start with selecting all groups except the given one;

SELECT
    GroupId, GroupName
FROM
    Groups
WHERE
    GroupId <> 2

Now, we will need a nested select statement to add the third column, I usually put the outer structure in place, and then I start writing the SELECT statement within the parenthesis:

SELECT
    GroupId, GroupName,
    (SELECT something) AS SharedMemberCount
FROM
    Groups
WHERE
    GroupId <> 2

Let's start with just counting the total numbers of members in each group, to do this the Groups table needs an alias so that we can distinguish the two GroupId columns;

SELECT
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2

This is the current resultset;

 

Now we need to filter the inner select so that it only counts the members that are also members in the given group (GroupId=2).

SELECT
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId
        AND pgm.PersonId IN 
            (SELECT PersonId
            FROM PersonGroupMemberships 
            WHERE GroupId=2)    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2

And really, this was no brain surgery. We simply say that the PersonId of the rows we count has to have a value that corresponds to any PersonId that is a member of group 2 by using the IN statement and yet another nested SELECT statement.

Next we should add sorting so that the groups with the most shared members comes on top, I also add a TOP statement since we probably just want the most related groups, not all groups. These changes wont make any difference with this testdata, but they would if we had more groups:

SELECT TOP 4
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId
        AND pgm.PersonId IN 
            (SELECT PersonId
            FROM PersonGroupMemberships 
            WHERE GroupId=2)    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2
ORDER BY
    SharedMemberCount DESC

And now we get the resultset we wanted;

 

To think about
Of course you wouldn't want to display the "Celebrities" group in this case, since it has no members in common with the "Superheroes" group. How could you filter the resultset so that it only includes groups where SharedMemberCount is more than zero? You might wanna use this technique.

(By the way, I have installed SQL Server 2008 Express, and it gives neat intellisense when working with SQL queries! :-)

How to get javascript intellisense for Asp.NET AJAX in a usercontrol (ascx) file

To get javascript intellisense for jQuery and your own scripts inside a usercontrol file you can use the following hack at the top of the ascx-file (after the @Control directive).

<% if (false) { %>
    <script src="scripts/jquery-1.3.2.js" type="text/javascript"></script>
    <script src="scripts/myScripts.js" type="text/javascript"></script>
<% } %>

This simply means that when you work with the file in Visual Studio, it will give you intellisense for these javascript libraries. But in runtime the if statement will be false so the scripts will not be included again (they shouldn't because they should already be included by the page that hosts the usercontrol. Remember, this hack is for Visual Studio javascript intellisense only).

But what about the Asp.NET AJAX framework (whit the $get, $addHandler methods etc...)? One workaround I figured out is to follow these steps:

1. Load an .aspx page that contains a ScriptManager in your browser.
   
2. View the source and copy out the src for a script tag that contains "ScriptResource.axd" (marked text in this image):
   
   
3. Paste this after localhost:port in the address field of the browser so that the script file loads (I only got this to work in Firefox):
   
   
4. If it starts with the comment in the image above, go on to the next step, if not return to step number two and get the next reference that includes "ScriptResource.axd".
   
5. Once you've found the MicrosoftAjax.debug.js file, copy the entire contents, paste it into a .js-file and put it in your project.
   
6. Now you can reference that file from your ascx file and get intellisense for Asp.NET AJAX even in a usercontrol file:

Finally, an appropriate quote: "Oh the troubles we put ourselves through for pretty IntelliSense.".

Introducing the Ajax ColumnListBoxExtender

Today I'm proud to present the first release of my first open source project; The Ajax ColumnListBoxExtender. The idea of the control is to give the possibility to layout an asp:ListBox with a lot of options in multiple columns instead of one single column.

Check out the project homepage at http://ajaxcolumnlistbox.codeplex.com and the live samples and documentation at http://ajaxcolumnlistbox.deap.nu.

I wont go in to the details of how this is developed, but it is open source so feel free to download the code from the project home page and have a look at it. I can say I've learned a lot both about javascript and Asp.NET AJAX developing this.

Lesson blog: Creating a plugin accepting application

Last week we had a lesson describing a simple sample of how you could create an application that accepts plugins, using an interface approach. This is my take on it;

First of all you need to decide quite exactly what the plugin developer should be possible to do. For this example I have created a simple Win Forms application that shows a list of contacts and allows the user to edit the contacts.

The Main Window of the application:

When the user double clicks a contact a simple editing form is opened:

When the user clicks Save this form is closed and any changes are reflected in the main window. But this form is quite dull and simple. Wouldn't it be cool if you could let some other developer attach a plugin to the application that will change the layout and possibly the behavior of this form? That's what we're going to do.

I wont go into the details of how the above application is built, if you want to follow along download this starting point here.

Some considerations has already been made when it comes to the plugin part, so lets have a look at the solution as it looks now;

PluginHost is the Windows Application and PluginLib is a Class Library that is referenced by PluginHost. PluginLib contains the Contact business class that has one string property for each peace of data on each contact (i.e. Firstname, Lastname etc). This is a separate project because the resulting dll is what will be shared both by the Host application and any Plugin "applications".

The host application is made up of two Form classes, MainWindow and DefaultContactEditor. Since the editor window might be replaced by a plugin some efforts has been made to keep as much logic as possible in the MainWindow class. Let's have a look att some of the code in the DefaultContactEditor class;

public Contact ContactToEdit { get; set; }
public event EventHandler ContactSaved;

public void LoadContact(Contact c)
{
    tbFirstname.Text = c.FirstName;
    tbLastname.Text = c.LastName;
    tbEmail.Text = c.Email;
    tbMSN.Text = c.MSN;
    tbHomepage.Text = c.Homepage;
    ContactToEdit = c;
}

private void SaveEditedContact()
{
    ContactToEdit.FirstName = tbFirstname.Text;
    ContactToEdit.LastName = tbLastname.Text;
    ContactToEdit.Email = tbEmail.Text;
    ContactToEdit.MSN = tbMSN.Text;
    ContactToEdit.Homepage = tbHomepage.Text;
    if (ContactSaved != null)
    {
        ContactSaved(this, EventArgs.Empty);
    }
}

The LoadContact method is called from the MainWindow when the edit window is opened, and it fills up the windows textboxes with values from the given Contact object and assigns this object to the class member ContactToEdit. It is placed in a separate method instead of in the constructor, because later on an interface will require this method (ideally the interface should require a constructor receiving a contact object but as far as I know that is not possible).

The SaveEditedContact is called when the user clicks the save button in the edit window. This method ends by triggering the ContactSaved event that the MainWindow is listening to.

Now, let's have a look at some of the code in the MainWindow class;

private void lvContacts_MouseDoubleClick(object sender, MouseEventArgs e)
{
    if (lvContacts.SelectedItems.Count > 0)
    {
        Contact selectedContact = (Contact)lvContacts.SelectedItems[0].Tag;
        DefaultContactEditor editor = new DefaultContactEditor();
        editor.LoadContact(selectedContact);
        editor.ContactSaved += new EventHandler(editor_ContactSaved);
        editor.Show();
    }
}

private void editor_ContactSaved(object sender, EventArgs e)
{
    ((Form)sender).Close();
    DisplayContacts();
}

The first method here is an event handler for the double click event for the ListView control that lists the contacts in the main window. After assuring that one item is selected I get a reference to the selected Contact object and creates a new instance of the edit window. Here you can see the call to LoadContact. Also, you can see that the ContactSaved event is attached to the editor_ContactSaved method. This method will ensure that the edit window is closed when the contact is saved and it calls a method - DisplayContacts - that will redraw the ListView control reflecting any changes that might has been made.

Now to the plugin fixing. First, let's create an interface from some of the members in the DefaultContactEditor class. I call this interface IContactEditor and put it in the PluginLib project;

public interface IContactEditor
{
    Contact ContactToEdit { get; set; }
    event EventHandler ContactSaved;

    void LoadContact(Contact c);
}

After that I make sure that the DefaultContactEditor implements this interface, by adding it to the inheritance list;

public partial class DefaultContactEditor : Form, IContactEditor

Nothing more is needed here since the interface members already are implemented in DefaultContactEditor.

The idea now is to change the code that opens the editor window so that it uses a plugin if available and if not it will use the DefaultContactEditor. Let's start with creating a helper class in the PluginHost application;

public static class PluginUtility
{
    private const string PLUGIN_PATH = @"Plugins\";

    public static IContactEditor GetEditorWindow()
    {
        IContactEditor result = null;

        try
        {
            foreach (string file in Directory.GetFileSystemEntries(PLUGIN_PATH, "*.dll"))
            {
                Assembly dllAssembly = Assembly.LoadFrom(file);

                foreach (Type t in dllAssembly.GetTypes())
                {
                    //Find class that inherits from Form and implements IContactEditor
                    if (t.IsPublic && !t.IsAbstract &&
                        t.IsSubclassOf(typeof(Form)) &&
                        t.GetInterface("IContactEditor") != null)
                    {
                        result = (IContactEditor)dllAssembly.CreateInstance(t.FullName);
                        break; //Exit foreach when plugin is found
                    }
                }

                if (result != null)
                    break; //Exit foreach when plugin is found
            }
        }
        catch { }

        //If result still is null, return default contact editor
        if (result == null)
        {
            result = new DefaultContactEditor();
        }

        return result;
    }
}

First we declare a constant containing the relative path to the folder where the plugin has to be placed. This folder has to be created in the output directory of the application, i.e. bin/Debug.

To keep the example simple this application will only accept one plugin. That one plugin, if existing, will be found in the GetEditorWindow method that returns an IContactEditor. In the first foreach we loop all dll-files within the Plugins directory. Foreach dll we loop the types that exists in the dll and using an if statement we look for classes that are public, not abstract, that inherits from Form and implements IContactEditor. If such a type is found we call the CreateInstance method of the Assembly object, which will create an instance of the found type. This instance is cast to an IContactEditor and assigned to the result variable. After that we make sure we exit both foreach loops. If no plugin is found, the last if will be true and the result variable will be assigned an instance of the DefaultContactEditor class.

Now, the double click event of the Main Windows ListView could be updated as follows;

private void lvContacts_MouseDoubleClick(object sender, MouseEventArgs e)
{
    if (lvContacts.SelectedItems.Count > 0)
    {
        Contact selectedContact = (Contact)lvContacts.SelectedItems[0].Tag;
        //OLD: DefaultContactEditor editor = new DefaultContactEditor();
        IContactEditor editor = PluginUtility.GetEditorWindow();
        editor.LoadContact(selectedContact);
        editor.ContactSaved += new EventHandler(editor_ContactSaved);
        //OLD: editor.Show();
        ((Form)editor).Show();
    }
}

Now lets build a plugin by adding another class library to the solution, I call it EditorPlugin. To speed things up I add a copy of the DefaultContactEditor class to this class library (make sure you change the namespace and the class name to EditorPlugin in the code view and in the designer.cs file!). Since this class inherits from Form I also need to reference System.Drawing and System.Windows.Forms which isn't included from the beginning in a Class Library (the compiler will inform you about this). We also need access to the Contact class and the IContactEditor interface; a reference to the PluginLib will solve this. After that I changed the design of the form as follows;

All that is altered here is a minor layout change, putting together the first and lastname textboxes on one single row. Now, lets build this class library and put the resulting EditorPlugin.dll in the Plugins directory of the PluginHost application.

Now when you run the application this restyled window will be used for editing. Download the complete solution here.

Try for yourself;

• Add a checkbox by the MSN field with the caption "Same as email", setting the MSN textbox to the same value of the email textbox when the checkbox is checked.
• What would you have to inform plugin developers about so that they will be able to develop a plugin for this application?
• Add a dialog in the application that let's the user add new contacts to the list. Make it possible for plugin developers to replace this dialog.

SQL: Select from your select - filtering on calculated columns

Consider this SQL that lists products from the Northwind database;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products

To the right you can see the resulting table. The last column shows a calculated value based on the UnitPrice column multiplied with 1,25.

Now, lets say you would like to filter the result including only products that has a UnitPriceWithVAT that is higher than 100.

You can't do like this;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products
WHERE
    UnitPriceWithVAT > 100 --This wont work

This will only give you the error message "Invalid column name 'UnitPriceWithVAT'.", i.e. the calculated column is not known in the WHERE clause.

There are a few different ways to solve this problem. The most obvious one is to repeat the calculation in the WHERE clause;

--Solution A
WHERE
    UnitPrice*1.25 > 100

But it's always good to avoid repeated code, so to accomplish that we could make use of a temporary table. In MS-SQL this is done with the INTO statement followed by a table name that starts with #. Then we can select from the temporary table and clean up;

--Solution B
SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT
INTO
    #tempTable    
FROM 
    Products

SELECT * FROM #tempTable WHERE UnitPriceWithVAT > 100

DROP TABLE #tempTable

However, there is an even more efficient and, in my opinion, more elegant way to do this;

--Solution C
SELECT * FROM
    (
        SELECT 
            ProductName, UnitPrice, 
            UnitPrice*1.25 AS UnitPriceWithVAT 
        FROM 
            Products
    ) AS t
WHERE
    UnitPriceWithVAT > 100

In this last version the inner SELECT statement is "turned into a table" that the outer SELECT selects from, making it possible to filter on the calculated column. The important part here is to give the inner SELECT a table alias (AS t), otherwise it will not work.

In this simple example it might seem easier to just repeat the calculation in the where clause (Solution A), but what if you have a SQL-statement with many calculated columns that also should be included in the filtering, and what if you change the calculation in the SELECT clause but forgets to do it in the WHERE clause?

All three solutions will give the same result. Which one do you prefer?