Mysql Join check IF data does not exist in another table

Asked

Viewed 415 times

0

TABLE "profile"

id  nome    
1   UserA   
2   UserB   
3   Usuario1    
4   Usuario2    
5   Usuario3    
6   Usuario4

TABLE "friends"

id  amigo1  amigo2  status  
 2      3       2       1   
 4      4       1       1   
 5      2       5       1   
 6      1       5       1   
 7      6       1       0

If I use Join, to return friends, it works OK (use as base id=1 / Usera)

SELECT perfil.id, perfil.nome,amigos.amigo1, amigos.amigo2, amigos.status FROM perfil
INNER JOIN amigos ON ((amigos.amigo1 = perfil.id) OR (amigos.amigo2 = perfil.id))
WHERE perfil.id != 1 AND (amigos.amigo1 = 1 or amigos.amigo2 = 1) AND amigos.status = '1' GROUP BY perfil.id ORDER BY rand() LIMIT 12

Call me back

id  nome     amigo1 amigo2  status  
 4  Usuario     4        1       1  
 5  Usuario3    1        5       1

My doubt is, I cannot return the values of those who are not friends of ID 1 / Usera

Join this way (applying in WHERE != 1 (ID 1):

SELECT perfil.id, perfil.nome, amigos.amigo1, amigos.amigo2, amigos.status FROM perfil
INNER JOIN amigos ON ((amigos.amigo1 = perfil.id) OR (amigos.amigo2 = perfil.id)) 
WHERE perfil.id != 1 AND (amigos.amigo1 != 1 or amigos.amigo2 != 1) GROUP BY perfil.id ORDER BY rand() LIMIT 12

Results:

id  nome    amigo1  amigo2  status  
6   Usuario4    6       1       0   
5   Usuario3    2       5       1   
4   Usuario2    4       1       1   
2   UserB       3       2       1   
3   Usuario1    3       2       1

My goal is that Join does not appear the User3 (ID 5) in the list, because it is listed as a friend of ID 1.

mysql

  • SELECT profile.id, profile.name, friends.friend1, friends.friend2, friends.status FROM profile LEFT JOIN friends ON ((friends.friend1 = profile.id) OR (friends.friend2 = profile.id)) WHERE profile.id != 1 AND (amigos.amigo1 IS NULL ) GROUP BY perfil.id ORDER BY Rand() LIMIT 12

  • Returned blank, no data recovered

  • William, I think I understood your misgivings. Try this: SELECT profile.id, profile.name, friends.friend1, friends.friend2, friends.status FROM profile INNER JOIN friends ON ((friends.friend1 = profile.id) OR (friends.friend2 = profile.id)) WHERE profile.id != 1 AND (friends.friend1 != 1 and friends.friend2 != 1) AND friends.status = '1'

  • @Lucasbrogni then my dear friend, unfortunately the result has not yet been expected (even if I make some adjustments removing the status, etc), still other users (ID 5 / User3 and userB duplicity) appear in the search, the removal of the grouping.

  • If it serves as a support for your collaboration, this script (Pastebin) is what I already use, it "serves" but is extremely ineffective https://pastebin.com/Jy0XsSaC (disregard unnecessary fields the question)

1 answer

1


/**
selecionar todos que nao sao amigos do id=1
**/
SELECT *
FROM perfil p
WHERE
 id!=1
AND
 id NOT IN (
 SELECT pa.id
 FROM perfil p
 JOIN amigos a ON (a.amigo1=p.id OR a.amigo2=p.id)
 JOIN perfil pa ON pa.id=IF(a.amigo1=p.id, a.amigo2, a.amigo1) 
 WHERE p.id=1
 AND a.status=1
);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.