Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>After having a look at the <a href="http://dev.mysql.com/doc/refman/5.0/en/subqueries.html" rel="nofollow">subquery documentation</a> I have managed to come up with a working solution.</p> <p>In this example A has a <code>members.id=8</code>.</p> <pre><code>SELECT * FROM members WHERE id IN ( SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.friend_uid IN ( SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.friend_uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 UNION SELECT m.id FROM friends f INNER JOIN members m on m.id=f.friend_uid WHERE f.uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 ) AND m.id&lt;&gt;8 AND f.confirmed_uid=1 AND f.confirmed_friend_uid=1 UNION SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.uid IN ( SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.friend_uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 UNION SELECT m.id FROM friends f INNER JOIN members m on m.id=f.friend_uid WHERE f.uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 ) AND m.id&lt;&gt;8 AND f.confirmed_uid=1 AND f.confirmed_friend_uid=1 ) AND id NOT IN ( SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.friend_uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 UNION SELECT m.id FROM friends f INNER JOIN members m on m.id=f.friend_uid WHERE f.uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 ); </code></pre> <p>This query runs nice and fast and returns friends of all A's friends, that are not already friends of A. It also ensure that friendships have been confirmed by both members in the relationship.</p> <p>There may well be a way to reduce the size of this query, as the same <a href="http://dev.mysql.com/doc/refman/5.0/en/subqueries.html" rel="nofollow">subquery</a> is used on three separate occasions in the query as a whole.</p> <pre><code> SELECT m.id FROM friends f INNER JOIN members m on m.id=f.uid WHERE f.friend_uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 UNION SELECT m.id FROM friends f INNER JOIN members m on m.id=f.friend_uid WHERE f.uid=8 AND confirmed_uid=1 AND confirmed_friend_uid=1 </code></pre> <p>If there is a way to only run this subquery once, please let me know. Otherwise I will edit the answer if/when I figure it out myself.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow do i create a MySQL Query for 'People you may know' (friends of friends)?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USNe0
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do i create a MySQL Query for 'People you may know' (friends of friends)?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COsubquery will yield you low performance; Joins are very efficient;
      singulars
      1. PO
      1. USlogan
 

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