How can I write a cross-site Data Explorer query that joins databases on linked accounts?

Question

The answer to this question implies that you can write cross-site Data Explorer queries, but the sample query doesn't appear to do what I think it should.

SELECT TOP 1 *
    FROM [StackOverflow.Exported]..[Users] u1
    INNER JOIN [ServerFault.Exported]..[Users] u2 ON u1.Id = u2.Id

I had assumed that this would find a linked account between Stack Overflow and Server Fault, but as far as I can tell user IDs are not preserved across the Stack Exchange network. Is it possible to write a query that joins linked accounts across Stack Exchange sites?

Answer 1

Here's a query to generate the comparison of AccountIds and the resulting query, which is of course interesting to run from different base sites :-)

(This answer used to refer to EmailHashes.)

Answer 2

I've taken the idea and query from Mark Hurd and removed the need to do a generate and copy-paste. You can run a multi-database query in one go.

Here is how. The EXEC() statement does take a T-SQL string and it will execute that SQL statement.

The STRING_AGG function takes a string expression and a seperator value that is placed between the string expressions.

When you use the STRING_AGG function to take its input from rows in a table, it will produce one string as the function aggregates the rows it is fed.

The string expression needs to be of the type NVARCHAR(max) otherwise you're greeted with the error

STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

and a tactical placed CONVERT(NVARCHAR(max), {field}) will resolve this problem.

Putting it all together will give you this version of the query

-- var to hold the complete query as text
declare @sql nvarchar(max)

-- use the STRING_AGG function to get the sql
Select @sql = STRING_AGG(
-- this is the string for each database
-- [Name] is here the name of the database
N'Select ''' + Replace([Name],'','') + N''' As [DatabaseName], '
+ N'Count(*) As [Count] '
+ N'From [' + convert(nvarchar(max),[Name]) + N']..Users fu Join Users u ON fu.AccountId=u.AccountId '
-- this is what gets used to concatenate all those strings
,N' UNION ALL ')
From sys.Databases
Where database_id > 5 -- skip the first 5 databases, they have nothing for us
and Name Like '%'

print @sql -- this gets shown on the Messages tab

exec(@sql) -- this executes the SQL statement that is the @sql variable

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the unforgettable Monica Cellio.
Say "Hi" in SEDE chat.