Databases
StackOverflow2013
Postsdbo
POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
    primarykey
    data
    text
    <p>The following query counts the common neighbors of two nodes in the graph:</p> <pre><code> DECLARE @monthly_connections_test TABLE ( calling_party VARCHAR(50) , called_party VARCHAR(50)) INSERT INTO @monthly_connections_test SELECT 'z1', 'z2' UNION ALL SELECT 'z1', 'z3' UNION ALL SELECT 'z1', 'z4' UNION ALL SELECT 'z1', 'z5' UNION ALL SELECT 'z1', 'z6' UNION ALL SELECT 'z2', 'z1' UNION ALL SELECT 'z2', 'z4' UNION ALL SELECT 'z2', 'z5' UNION ALL SELECT 'z2', 'z7' UNION ALL SELECT 'z3', 'z1' UNION ALL SELECT 'z4', 'z7' UNION ALL SELECT 'z5', 'z1' UNION ALL SELECT 'z5', 'z2' UNION ALL SELECT 'z7', 'z4' UNION ALL SELECT 'z7', 'z2' SELECT monthly_connections_test.calling_party AS user1, monthly_connections_test_1.calling_party AS user2, COUNT(*) AS calling_calling, 0 AS calling_called, 0 AS called_calling, 0 AS called_called, 0 AS both_directions FROM @monthly_connections_test AS monthly_connections_test INNER JOIN @monthly_connections_test AS monthly_connections_test_1 ON monthly_connections_test.called_party = monthly_connections_test_1.called_party AND monthly_connections_test.calling_party &lt; monthly_connections_test_1.calling_party GROUP BY monthly_connections_test.calling_party, monthly_connections_test_1.calling_party </code></pre> <p>For the following graph <img src="https://i.stack.imgur.com/FMIQm.jpg" alt="alt text"></p> <p>it returns the number of common neighbors which are called by user1 AND user2 so for example the number of neighbors called by z1 AND z2 it returns 2 as both call z4 and z5.</p> <p>Another thing I would like to count is the number of all neighbors of two nodes (users) which are called either by user1 or user2 so for example for the pair (z1, z2) the query should return 5 (user z1 calls z2, z3, z4, z5, z6 and user z2 calls z1, z4, z5, z7 - the connections between z1 and z2 have to be exluded as (z1, z2) is the observed pair and the number of elements in (z3, z4, z5, z6) U (z4, z5, z7) is 5).</p> <p>Does anyone know how to modify/create the join query for the above logic?</p> <p>Thank you!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNiko Gamulin
    1. USNiko Gamulin
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. USIamIC
      1. VTFavorite
    1. COFor such questions it is better to have a script with: 1) Create table and 2) Inserts. It would be easier to play with data and find optimal way faster. Thanks
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. USGenius
    2. COThank you for the suggestion!
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. USNiko Gamulin
    3. COI strongly recommend using abbreviated table aliases rather than repeating the table name at full length: `@monthly_connections_test AS mct`. In the context, I'd use `mct1` and `mct2`, or even `m1` and `m2`. It makes the SQL more readable (and helps you avoid horizontal scroll bars - but that's less important than the improved readability!).
      singulars
      1. POHow to use JOIN instead of UNION to count the neighbors of "A OR B"?
      1. USJonathan Leffler
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload