CODEWITHSUNDEEP
sqlexcelcross-join
Maheen Siddiqui
Maheen Siddiqui

Reputation: 539

Cross join in Excel or SQL

I am using Gephi to create a network graph, here is a small subset of the data that I have:

ID  Label
1   Sleep quality   
2   Stress  
3   Healthy Eating  
4   Tremor  
5   Balance 
6   Drooling    
7   Exercise    
8   Mood    
9   Speech  
10  Parkinson's On-Off

So I want my graph to have these 10 nodes.

Then for the edges, I have:

Source Target   User
1       5       5346
5       4       5346
4       7       5346
7       6       5346
6       9       5346
9       3       5346
3       2       5346
2       8       5346
8       10      5346

The "User" column is something I have added to explain the problem I am having. I am using a big database (in SQL) to obtain this data. On a mobile phone app, users select 10 of the different choices available (as listed in the nodes). In SQL I can query the data easily so that I can obtain the 10 choices of each of the users.

It is easy to create a graph with the edges with the information in the edges table but I would also like to connect each edge to all other edges, this is important for me. So for example, 1 connects to all those in "target". Then 5 connects to all those in "target" and so until all nodes are connected to each other for each user.

I can do this manually but the original data set has 2000+ users and this will take a long time. I know that there is a way of using cross join, possibly in Excel or in SQL... but I'm unsure how to do this..

Thanks!

Upvotes: 0

Views: 797

Answers (1)

abraxascarab
abraxascarab

Reputation: 701

You can drop this cross join into your SQL: (It'll list all Source's with all possible Target's.)

   (SELECT e.Source as Source, n.ID as Target
      FROM
      (SELECT DISTINCT Source FROM tblEdges) as e
      cross join (SELECT DISTINCT ID FROM tblNodes) as n
      ) as xCross

Upvotes: 1

Related Questions