I have a database in DB Browser where I have two tables;
x
CREATE TABLE "User" (
"ID" integer NOT NULL,
"name" TEXT NOT NULL,
PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE "Friends" (
"UserID" INTEGER NOT NULL,
"FriendID" INTEGER NOT NULL,
FOREIGN KEY("UserID") REFERENCES "Anvandare"("ID") ON DELETE CASCADE
);
So my question is, how do I make a query that Displays People that I’m not friends with, but my friends are friends with them. It’s so I can find new friends basically.
Advertisement
Answer
You can join multiple times:
select distinct u.id, u.name
from friends f1 -- my friends
inner join friends f2 on f2.userid = f1.friendid -- the friends of my friends
inner join users u on u.id = f2.friendid
where
f1.userid = ?
and f2.friendid <> f1.userid
and not exists ( -- that are not my friends
select 1 from friends f3 where f3.userid = ? and f3.friendid = f2.frienid
)