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.
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
– Lucas Brogni
Returned blank, no data recovered
– Guilherme
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'
– Lucas Brogni
@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.
– Guilherme
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)
– Guilherme