SharePoint inner join lists

There are a lot of ways of doing your dev/database/IT job.There's ways that are more ideal and others that are less so.Sometimes doing things the more ideal way may take you way to long for so many different reasons.

One major reason to things quick and "not officially supported" is that you inherited somebody else's starter code.Nobody worth their salt has time to re-invent the wheel.In this situation you might be better off creating a workaround on the small item and get back to slaying dragons.

This post is about the workaround, not dragon slaying.

It is never recommended to query SQL Server to pull data from a SharePointlist.Nonetheless people still do it.If this list has a field of type "person or group" things get a bit more complicated.Normally list data is stored in a table called [AllUserData].The custom field data is stored in an XML field in this tabled named [tp_ColumnSet].You can query text and whatnot from there as though it's a field on the actual table.Not super fast but it is super functional!

You get thrown a curve ball however if you have a field for an Active Directory "Person or Group".At this point you need to fold in 2 more tables.Those being [UserDataJunctions] and [UserInfo].

Below is a quick, sloppy, non-optimized query to get you the first person from the "Person or Group" field in your SharePoint list. I say "first" because you may be able to store more than one person in that field. However, this isn't my dragon so I'm just pulling the first one and getting back to my day job. If you're reading this you're either in the same boat as I am or you don't know enough yet to do things more ideally so here you go.

SELECT

[SELECT

TOP 1

REPLACE[UI.tp_Login, 'i:0#.w|', '']

FROM

AllUserData UD2

INNER JOIN UserDataJunctions UDJ ON

UDJ.tp_DocId = UD2.tp_DocId AND UD2.tp_SiteId = UDJ.tp_SiteId AND UD2.tp_ListId = UDJ.tp_SourceListId AND UD2.tp_ParentId = UDJ.tp_ParentId

INNER JOIN UserInfo UI ON UD2.tp_SiteId = UI.tp_SiteID AND UDJ.tp_Id = UI.tp_ID

WHERE

UD2.tp_ID = UD.tp_ID

AND UD2.tp_ListId = UD.tp_ListId

]

AS LoginName,

UD.*

FROM

AllUserDataUD

WHERE

UD.tp_ListId = 'YOUR GUID FOR THE LIST'

#sharepoint#sqlserver#tsql #lists