11

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?

3
  • 1
    You could probably match them by comparing the EmailHash column. Most users (~98% or so) use the same email address for each account. – uɐɯsO uɐɥʇɐN Oct 16 '11 at 1:40
  • 1
    Yeah, the Data Explorer doesn't have any association information, so EmailHash would be the way to go. You could get associated accounts from the API (or the profile page), and then manually enter IDs for given sites into your query, but that's rather tedious. – Tim Stone Oct 16 '11 at 6:43
  • Dynamic cross-site queries seem impossible without some kind of hideous and difficult hack... I've been trying to figure out something with EXEC and CREATE FUNCTION, but this is way beyond my limited MSSQL ability. – Jeremy Jan 28 '12 at 23:47
5
+200

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.)

0
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .