CODEWITHSUNDEEP
mysqlsql
SebT
SebT

Reputation: 45

mySQL reference correlation in a subquery with join

I'm working on a small social network service.

Very classically, I have 3 tables :
- table "circles_users" : all users that belong to the same "circle"
- table "friends" : friendship relation between users
- table "checkin" : is a user "checkin" somewhere or not

Here the structure of the database : http://sqlfiddle.com/#!2/27888/1

I would like to ask my database to give me :
all users from a specific "circle" with for each of them :
- the number of friends this user has in common with user id = 3
- if this user is checkin or not

Here what I'm trying to do :

SELECT a.uid, 
(
    SELECT COUNT(*)
    FROM (SELECT IF (uid1 = 3, uid2, uid1) AS cf FROM friends WHERE (friends.uid1 = 3 OR friends.uid2 = 3 )) as b 
    JOIN friends ON ((friends.uid1 = b.cf AND friends.uid2 = a.uid) OR (friends.uid1 = a.uid AND friends.uid2 = b.cf))
) as common_friends, 
checkin.status as checkin_status 
FROM 
(SELECT circles_users.uid FROM circles_users WHERE circles_users.circlename = 'circle_A') as a
LEFT JOIN checkin ON checkin.uid = a.uid



I get this error message : Unknown column 'a.uid' in 'on clause'
It has been now 2 days that I'm trying to fix this unsuccessfully.

It seems that it is not possible to reference correlation name in my subquery.
For instance, if I replace a.uid in the subquery by a specific uid (for instance let's say '4'), I don't get any error. But of course, the result is false...

Is there someone who could help me ? That would be very nice :)

OTHER OPTION TO FOLLOW ?

Another option would be to pass the "common_friends" subquery as a join.
I tried to do something like this :

SELECT a.uid, 
c.cnt as common_friends, 
checkin.status as checkin_status 
FROM 
(SELECT circles_users.uid as uid FROM circles_users WHERE circles_users.circlename = 'circle_A') as a
LEFT JOIN 
    (
        SELECT DISTINCT COUNT(*) as cnt 
        FROM (SELECT IF (uid1 = 3, uid2, uid1) AS cf FROM friends WHERE (friends.uid1 = 3 OR friends.uid2 = 3 )) as b 
        JOIN friends ON ((friends.uid1 = b.cf AND friends.uid2 = a.uid) OR (friends.uid1 = a.uid AND friends.uid2 = b.cf))
    ) as c ON 1=1 
LEFT JOIN checkin ON checkin.uid = a.uid



But again : I get this error message : Unknown column 'a.uid' in 'on clause'
Anyway, do you think this version would be easier to handle and would open new possibilities to resolve my problem ?

If you want to play with my queries : (thanks to @zundarz)

http://sqlfiddle.com/#!2/27888/1


Upvotes: 1

Views: 159

Answers (1)

asontu
asontu

Reputation: 4659

Rewrote a query based on the info you give and what you are looking for.

SELECT cu.uid, COUNT(f.uid1) common_friends, c.status
FROM circles_users cu
LEFT JOIN friends f
    ON (f.uid1 = cu.uid OR f.uid2 = cu.uid)
   AND f.status = "on"
   AND IF (f.uid1 = cu.uid, f.uid2, f.uid1) IN (
      SELECT IF (uid1 = 3, uid2, uid1)
      FROM friends
      WHERE status = "on"
        AND (friends.uid1 = 3 OR friends.uid2 = 3)
   )
LEFT JOIN checkin c
    ON c.uid = cu.uid AND c.status = "on"
WHERE cu.circlename = "circle_A"
GROUP BY cu.uid

Example sqlFiddle

Upvotes: 1

Related Questions