CODEWITHSUNDEEP
phpmysqlsql
Den R.
Den R.

Reputation: 135

MySQL: Using union to check if two columns contain eachother's values?

I have a friend table with two primary columns (uid and fid) and am essentially trying to see if for example if the friend is also friends of the user, so

uid 5 -> fid 6
uid 6 -> fid 5

I had tried to mix and mash SELECT queries to somehow return a deciding result, however cannot seem to find anything. count() will return either one or two columns (two being correct), however PHP's PDO has no good means to find number of selected (not affected) rows without hackish loops.

(SELECT count(uid) FROM friends WHERE uid = 1 AND fid = 2)
  UNION ALL -- Or plain union
(SELECT count(uid) FROM friends WHERE uid = 2 AND fid = 1)

This of course still returns one or two rows of 0/1 or 0 or 1

Can you think of a way to return either 1 or 0 in MySQL in the first column? or a better way of writing this functionality in to my program? (I try best to avoid hackish things)

Upvotes: 1

Views: 1514

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

SELECT a.uid, a.fid
FROM friends a
  JOIN friends b
     ON a.fid = b.uid
    AND a.uid = b.fid
WHERE a.uid = 1
  AND a.fid = 2

To avoid "duplicate" rows.. e.g. 2 rows for every couple of friends, add the condition a.uid < b.uid:

SELECT a.uid, a.fid
FROM friends a
  JOIN friends b
     ON a.fid = b.uid
    AND a.uid = b.fid
    AND a.uid < b.uid
WHERE a.uid = 1
  AND a.fid = 2

So, to count all friendships, use:

SELECT COUNT(*) AS countAllFrienships
FROM friends a
  JOIN friends b
     ON a.fid = b.uid
    AND a.uid = b.fid
    AND a.uid < b.uid

So, to count friends of user X, use:

SELECT a.uid
     , COUNT(*) AS numberOfFriends
FROM friends a
  JOIN friends b
     ON a.fid = b.uid
    AND a.uid = b.fid
WHERE a.uid = X
GROUP BY a.uid

Upvotes: 2

Cyril Gandon
Cyril Gandon

Reputation: 17058

You need an auto join to return all the couple user/friend which are in a bijection friendship :

SELECT f.uid, f.fid
FROM Friend f
    INNER JOIN Friend ff
       ON f.uid = ff.fid
       AND f.fid = ff.uid

Be aware that it will return 'duplicates', in you case :

uid  |  fid
5       6
6       5

Upvotes: 4

Marc B
Marc B

Reputation: 360702

Why not a single query?

select count(*)
from friends
where ((uid = 1) and (fid = 2)) or ((uid = 2) and (fid = 1))

Note that you have to do a count(*), as the UID's will be different if there's a mutual friendship. You'd get two rows with a count of 1, instaed of a single row with a count of 2.

Upvotes: 2

Related Questions