You are hereINNER JOIN on the Same Table

INNER JOIN on the Same Table

By hagrin - Posted on 04 January 2011

Here's something that always bugged me that I finally was able to figure out today (that in retrospect is so simple I don't understand why I struggled so much with it before).

If you've ever looked at the data stored in a SharePoint 2007 / WSS v3 database, you know that the AllUserData table stores an immense amount of list data. Say you had two lists - a list of Employees and a list of Tasks and in the Tasks list you had a Lookup field using the Employees list. You would have a setup like this to ensure data validation, manage a list of employees in a single location and other data structure reasons. So, how would you write a single, direct SQL query against the AllUserTable to return data in two different lists?

Look at the following query -

SELECT a.tp_ID, a.nvarchar1, a.nvarchar3, a.nvarchar4, a.nvarchar5, a.datetime1, b.nvarchar1, a.ntext3, a.tp_Modified
FROM AllUserData a
INNER JOIN AllUserData b ON a.int2 = b.tp_ID AND b.tp_ListID = '{a234a2a6-dbf2-4abf-b1d4-1544305b9cde}'
WHERE a.tp_ListId = '{4BE243EE-E100-4060-9921-840DEB0F54B0}' and a.tp_IsCurrent = '1' and a.nvarchar4 <> 'Completed'
and a.tp_Modified < '12/1/2010' and a.tp_ContentType = 'Item' and a.tp_DeleteTransactionID = 0x
ORDER BY b.nvarchar1, a.tp_Modified

There's two important aspects in the above query that you need to apply in your own queries. First, notice that there is an INNER JOIN statement that joins AllUserData a and AllUserData b. You effectively name the same table twice and designate it with an alias to differentiate the two lists you are trying to join. Second, notice in the INNER JOIN statement that there is an AND operator that allows you to focus your INNER JOIN on only relevant data inside that table.

Hopefully, this little code snippet helps you in your SharePoint development and SQL query writing.