CODEWITHSUNDEEP
mysqlsql
Sourav
Sourav

Reputation: 17530

MySQL: Joining a table with itself without using Distinct

This is my table structure

pk [fUID, UID]

I want to get common friends of 2 persons. I tried:

SELECT t1.fName,
       t1.fUID 
  FROM friends t1, 
       friends t2 
 WHERE t1.UID = X 
   AND t2.UID = X2

but it did not work :( I'm getting same results repeatedly.

Some sample data 

UID  Name  
----------
1    Mark  
2    Eduardo  
3    Dustin

Friend Table 

UID  fUID  fName  
------------------
1    2     Eduardo   
2    1     Mark  
3    1     Mark  
1    3     Dustin

Upvotes: 1

Views: 209

Answers (5)

David Nelson
David Nelson

Reputation: 3714

If I understand you correctly, you have two user IDs (X1 and X2), and you want to find all of the people who are friends with both of those people.

SELECT f1.fUID, f1.fName
FROM friends f1,
friends f2
WHERE f1.UID = X1
      AND f2.UID = X2
      AND f1.fUID = f2.fUID

Note: this assumes that all friend relationships are stored twice in the friends table, once in each direction. If not, it gets a little more complicated.

Upvotes: 1

piotrpo
piotrpo

Reputation: 12626

select fName
from friends
where uid = FIRST_USER_ID
  and exists (
    select 1
    from friends sfriends
    where sfriends.fid = friends.fid
      and sfriends.uid = SECOND_USER_ID
);

Upvotes: 0

Andriy M
Andriy M

Reputation: 77707

SELECT
  f.fUID,
  u.Name
FROM (
  SELECT fUID
  FROM (
    SELECT
      CASE WHEN UID  IN (@X, @X2) THEN UID ELSE fUID END AS UID,
      CASE WHEN fUID IN (@X, @X2) THEN UID ELSE fUID END AS fUID
    FROM friends
    WHERE     (UID IN (@X, @X2) OR  fUID IN (@X, @X2))
      AND NOT (UID IN (@X, @X2) AND fUID IN (@X, @X2))
  ) s
  GROUP BY fUID
  HAVING COUNT(DISTINCT UID) = 2
) f
  INNER JOIN users u ON f.fUID = u.UID

Upvotes: 0

Chandu
Chandu

Reputation: 82933

Try this:

   SELECT DISTINCT t1.fName,t1.fUID 
     FROM friends t1, friends t2 
    WHERE t1.UID=<USER-ID-1>
      AND t2.UID=<USER-ID-2>
      AND t1.fUID = t2.fUID
      AND t1.UID <> t2.UID

Test Data setup:

CREATE TABLE Friends
(
    uID INT,
    fuID INT,
    fName VARCHAR(20)
)

INSERT INTO Friends
SELECT '1 ',' 2 ','Eduardo'
UNION
SELECT '2 ',' 1 ','Mark'
UNION
SELECT '3 ',' 1 ','Mark'
UNION
SELECT '1 ',' 3 ','Dustin' 


SELECT DISTINCT t1.fName,t1.fUID 
    FROM Friends t1, Friends t2 
 WHERE t1.UID=2
   AND t2.UID=3
     AND t1.fUID = t2.fUID

Upvotes: 0

Gaurav Agrawal
Gaurav Agrawal

Reputation: 4431

SELECT t1.fName, t1.fUID FROM friends t1 WHERE t1.UID=X1
Union
SELECT t1.fName, t1.fUID FROM friends t1 WHERE t1.UID=X2

you can fetch mutual friends by using union query.

Upvotes: 0

Related Questions