Databases
StackOverflow2013
Postsdbo
POEliminate tuples with reverse relation and no primary Key
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEliminate tuples with reverse relation and no primary Key
    text
    copied!<p>I am trying to insert all the relations based on the question below. I have got as far, to get all the relations from A to C (as per the question below). But as for I get, I am also getting the records getting "C friends with A". As far as I understand the question statement "No duplicate friendships", I have to insert friendship as A to C and not vice-versa. Either I understand the problem wrong, or I can't get the desired result.<br> So, when I try to insert into the table all the values I've got, my result is wrong. Some persons have friends that are more than desired, by a value of 2.</p> <p>The structure of the tables is as follows:</p> <pre><code>Friend ( ID1, ID2 ) </code></pre> <p>The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).</p> <p>(No primary key) The situation I am trying to solve is:</p> <p>"For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. Do not add duplicate friendships, friendships that already exist, or friendships with oneself."</p> <p>I have been trying to solve this problem for 2 days now. Please help.</p> <p>Thanks in advance.</p> <p>----My SQL query-----<br></p> <pre><code>select B.ID1 as ID1,B.ID3 as ID2 from (select A.ID1 as ID1,A.ID2 as ID2,A.ID3 as ID3,F3.ID2 as ID4 from (select F1.ID1 as ID1,F1.ID2 as ID2,F2.ID2 as ID3 from Friend F1 join Friend F2 on F1.ID2=F2.ID1 where F1.ID1&lt;&gt;F2.ID2) A join Friend F3 on A.ID3=F3.ID1) B where B.ID1&lt;&gt;B.ID4 group by B.ID1,B.ID3 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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