Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As Mike's answer interprets, and so do I, your table structure is something like</p> <pre><code>create table Friends ( user_Id1 int, user_Id2 int, status int); </code></pre> <p>What you appear to want is a DISTINCT list of friends that are either friends of yours OR friends DIRECTLY ASSOCIATED with your friends (ie: 1 degree of separation from you). So, lets go with this scenario. </p> <p>You are person ID 1111 and have friends 2222 and 3333. </p> <p>Person 2222 has friends of 1111 (you), 3333 (your other friend) and 4444 (new person). </p> <p>Person 3333 has friends of 1111 (you), 4444 (same as person 3333's friend -- coincidental), and 5555.</p> <p>Now, 2nd degree of separation (not what you are looking for) is that person 4444 has friend of 6666, 7777, 8888. You don't care about these others (6666, 7777, 8888)</p> <p>You are looking for the entire list of friends that are not you, and just want to see Friends 2222, 3333, 4444, 5555.</p> <p>I would start with a list of just your friends and use that as a basis to get THEIR friends. The inner-most query is to just get your distinct friends (without hard-coding who YOUR friends are). Then from that, get all their friends. If they happen to have similar, the "DISTINCT" will filter that out for you. After those are selected, get a UNION of your direct friends to represent "AllFriends". By using the IF(), we want whoever the "other" person is based on the join qualification. If the person joined on is in position 1, then we want the OTHER person, and vice-versa. Once you have your distinct list of friends, THEN join that to the users table for getting their name, picture and any other profile info.</p> <pre><code>select AllFriends.FinalFriendID, CONCAT(U.User_FirstName, " ", U.User_LastName) AS User_FirstName, U.User_ProfilePic from ( select DISTINCT IF( F2.User_ID1 = YourDirectFriends.PrimaryFriend, F2.User_ID2, F2.User_ID1 ) as FinalFriendID from ( select DISTINCT IF( F.User_ID1 = YourID, F.User_ID2, F.User_ID1 ) as PrimaryFriendID from Friends F where F.user_ID1 = YourID OR F.User_ID2 = YourID ) YourDirectFriends JOIN Friends F2 ON YourDirectFriends.PrimaryFriendID = F2.User_ID1 OR YourDirectFriends.PrimaryFriendID = F2.User_ID2 UNION select DISTINCT IF( F.User_ID1 = YourID, F.User_ID2, F.User_ID1 ) as FinalFriendID from Friends F where F.user_ID1 = YourID OR F.User_ID2 = YourID ) ) as AllFriends JOIN Users U on AllFriends.FinalFriendID = U.User_ID </code></pre> <p>Oh yeah, add in your qualifier of "Status" where applicable, and your limit requirement.</p>
    singulars
    1. This table or related slice is empty.
    1. POMysql Query Help for Recommended friends
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDRapp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHey thanks for your answer, I really liked your way of doing this but unfortunately find Rayn's way easy and quicker to fix my problem because I was wanting the schema redesign. But I would highly appriciate if you can tell me something about your and Rayn's way? which one would be more efficient, in fact if I try to use your way with the changes schema, thanks :)
      singulars
      1. PO
      1. USJunaid
    2. CO@Junaid, Ryan's way may be easier to read, but probably less efficient as WHERE IN (subqueries) have traditionally been more intensive on performance. It has to run each WHERE IN for everey record. That is why my inner join pre-queries all the 'My Friends'. and uses that as a basis to work with for subsequent joins. In addition, it also sounded like you wanted the immediate friends of your friends too, but not duplicated. I'll see if I can break it down more later, but I'd try to run explain on them with your data, and run some with timings and check results too.
      singulars
      1. PO
      1. USDRapp
 

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