Как найти друга в sql

@Juan Romero and @dasblinkenlight have already given great answers to this. Below are a couple of more complex options should they be required. i.e. allowing you to easily switch between friend-of-friend-of-friend to friend-of-friend-of-friend-of-friend with no recoding, and to exclude a friend-of-friend-of-friend where they’re also a friend-of-friend.

declare @userId long = 1 --whichever user's friends you're looking for
, @degreesOfSeparation = 3 --friend of a friend of a friend
, @name nvarchar(32) = 'John'

; with relationshipCte as 
(

    --get our patient 0 (the user whose friends we're looking for)
    select 0 degreesOfSeperation
    , user_id
    from [user]
    where user_id = @userId

    full outer join 

    --for every friend, pull back that friend's friends
    select degreesOfSeperation + 1
    , friend_id
    from relationshipCte cte
    inner join [Friends] f 
        on f.user_id = cte.user_id
        --and not f.friend_id = @userId --include this if we want to exclude the original user as being a friend of a friend (and similar)

)
select *
from relationshipCte cte
inner join [user] u
on u.user_id = cte.user_id
where cte.degreesOfSeperation = @degreesOfSeperation
and u.Name = @name
order by u.user_id
option (maxrecursion 10) --never go more than 10 relationships away from patient 0 (arguably a reasonable assumption given https://en.wikipedia.org/wiki/Six_degrees_of_separation)

If you want to avoid including any friends who are closer than 3 degrees of separateion (e.g. you’re friends with Jim and Jane; Jim and Jane are friends with one another; thuse they’re both friends, and also friends of friends), use the below:

declare @userId long = 1 
, @degreesOfSeparation long = 3
, @i long = 1
, @name nvarchar(32) = 'John'

declare @relationships table 
(
    user_id long
    , closestRelation long
)
insert @relationships (user_id, closestRelation) values (@userId, 0) --insert patient 0
while @i <= @degreesOfSeperation
begin
    insert @relationships (user_id, closestRelation) --insert friends
    select f.friend_id, @i
    from @relationships
    inner join Friends f
    on f.user_id = u.user_id
    where not exists (select top 1 1 from @relationships r where r.user_id = f.friend_id) --exclude friends already in our list
   SET @i = @i + 1
end

select *
from @relationships r
inner join [user] u
on u.user_id = r.user_id
where r.closestRelation = @degreesOfSeperation
and u.Name = @name
order by u.user_id

NB: I’ve not had a chance to run the above SQL; but hopefully should work as stated…

Описание :

У меня есть таблица пользователей как следующая

студенты

id ------ name     
1  ------ John    
2  ------ Sarah    
3  ------ Peter

и таблица друзей как

Друзья

person1 ------  person2    
1       ------  2
2       ------  3

Теперь я хочу всех друзей 2 и всех других моих друзей, чьи друзья не мои друзья.

Например, «люди, которых вы знаете», мы видим людей, которые являются друзьями наших друзей, но не являются нашими друзьями

Я успешно написал запрос, чтобы найти всех своих друзей, но я не уверен, как найти «друзей моих друзей» в одном запросе

Есть ли способ сделать это в одном запросе….

Я нахожу таких друзей

 select * from 'students' join 'buddy_circle' on 
'$reg_no' = 'person_1' and 'stregno' = 'person_2' or 
'$reg_no' = 'person_2' and 'stregno' = 'person_1'

где stregno — идентификатор студента, а buddy_circle — таблица друзей, а $regno — идентификатор пользователя

Многотабличные запросы

В предыдущих статьях описывалась работа только с одной таблицей базы данных.
В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их.
В данной статье вы узнаете основные способы соединения таблиц.

Например, если мы хотим получить информацию о тратах на покупки, мы можем её получить следующим образом:

SELECT family_member, amount * unit_price AS price FROM Payments


В поле family_member полученной выборки отображаются идентификаторы записей из таблицы FamilyMembers, но для нас они мало что значат.
Вместо этих идентификаторов было бы гораздо нагляднее выводить имена тех, кто покупал (поле member_name из таблицы FamilyMember).

Ровно для этого и существует объединение таблиц и оператор JOIN.



Общая структура многотабличного запроса
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2
    ON условие_соединения
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n
    ON условие_соединения]


Как можно увидеть по структуре, соединение бывает:

внутренним INNER (по умолчанию)
внешним OUTER, при этом внешнее соединение делится на левое LEFT, правое RIGHT и полное FULL

С более подробными деталями, чем отличается внутреннее соединение от внешнего и как они работают, мы познакомимся в следующих статьях.
Пока нам достаточно лишь знать, что для вышеописанного примера с запросом на покупки нам понадобится именно запрос с внутренним соединением,

который будет выглядеть следующим образом:
SELECT family_member, member_name, amount * unit_price AS price FROM Payments
INNER JOIN FamilyMembers ON Payments.family_member = FamilyMembers.member_id


В данном запросе мы сопоставляем записи из таблицы Payments и записи из таблицы FamilyMembers.
Чтобы сопоставление работало, мы указываем как именно

записи из двух разных таблиц должны находить друг друга. Это условие указывается после ON:
ON Payments.family_member = FamilyMembers.member_id


В нашем случае поле family_member указывает на идентификатор в таблице FamilyMembers и таким образом помогает однозначному сопоставлению.

В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле),

однако точно так же можно использовать и другие операторы сравнения.

SQL — Simple Query Language, то есть «простой язык запросов». Его создали, чтобы работать с реляционными базами данных. В таких базах данные представлены в виде таблиц. Зависимости между несколькими таблицами задают с помощью связующих — реляционных столбцов.

Когда запрашиваем данные из одной таблицы, работа со связующими столбцами не нужна. Но если нужно агрегировать данные из нескольких, стоит описать правила: как будут связаны строки на основе значений связующих столбцов. Тогда на помощь и приходит оператор join.

Что такое оператор join в SQL

Join — оператор, который используют, чтобы объединять строки из двух или более таблиц на основе связующего столбца между ними. Такой столбец еще называют ключом.

Предположим, что у нас есть таблица заказов — Orders:

OrderID	CustomerID	OrderDate
304101	21	10-05-2021
304102	34	20-06-2021
304103	22	25-07-2021

И таблица клиентов — Customers:

CustomerID	CustomerName	ContactName
21	Балалайка Сервис	Иван Иванов
22	Рога и копыта	Семён Семёнов
23	Редиска Менеджмент	Пётр Петров

Столбец CustomerID в таблице заказов соотносится со столбцом CustomerID в таблице клиентов. То есть он — связующий двух таблиц. Чтобы узнать, когда, какой клиент и какой заказ оформил, составьте запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
JOIN Customers 
ON Orders.CustomerID=Customers.CustomerID;

Результат запроса будет выглядеть так:

OrderID	CustomerName	OrderDate
304101	Балалайка Сервис	10-05-2021
304103	Редиска Менеджмент	25-07-2021

Общий синтаксис оператора join:

JOIN <Название таблицы для присоединения> ON <Условие присоединения на основе связующих столбцов>

Соединять можно и больше двух таблиц: к запросу добавьте еще один оператор join. Например, в дополнение к предыдущим двум таблицам у нас есть таблица продавцов — Managers:

OrderID	ManagerName	ContactDate
304101	Артём Лапин	05-05-2021
304102	Егор Орлов	15-06-2021
304103	Евгений Соколов	20-07-2021

Таблица продавцов связана с таблицей заказов столбцом OrderID. Чтобы в дополнение к предыдущему запросу узнать, какой продавец обслуживал заказ, составьте следующий запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, Managers.ManagerName 
FROM Orders 
JOIN Customers 
ON Orders.CustomerID=Customers.CustomerID 
JOIN Managers 
ON Orders.OrderId=Managers.OrderId

Результат:

OrderID	CustomerName	OrderDate	ManagerName
304101	Балалайка Сервис	10-05-2021	Артём Лапин
304103	Редиска Менеджмент	25-07-2021	Евгений Соколов

Внутреннее соединение INNER JOIN

Если использовать оператор INNER JOIN, в результат запроса попадут только те записи, для которых выполняется условие объединения. Еще одно условие — записи должны быть в обеих таблицах. В финальный результат из примера выше не попали записи с CustomerID=23 и OrderID=304102: для них нет соответствия в таблицах.

Общий синтаксис запроса INNER JOIN:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Слово INNER в запросе можно опускать, тогда общий синтаксис запроса будет выглядеть так:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Внешние соединения OUTER JOIN

Если использовать внешнее соединение, то в результат запроса попадут не только записи с совпадениями в обеих таблицах, но и записи одной из таблиц целиком. Этим внешнее соединение отличается от внутреннего.

Указание таблицы, из которой нужно выбрать все записи без фильтрации, называется направлением соединения.

LEFT OUTER JOIN / LEFT JOIN

В финальный результат такого соединения попадут все записи из левой, первой таблицы. Даже если не будет ни одного совпадения с правой. И записи из второй таблицы, для которых выполняется условие объединения.

Синтаксис:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Пример:

Таблица Orders:

OrderID	CustomerID	OrderDate
304101	21	10-05-2021
304102	34	20-06-2021
304103	22	25-07-2021

Таблица Customers:

CustomerID	CustomerName	ContactName
21	Балалайка Сервис	Иван Иванов
22	Рога и копыта	Семён Семёнов
23	Редиска Менеджмент	Пётр Петров

Запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
LEFT JOIN Customers 
ON Orders.CustomerID=Customers.CustomerID;

Результат:

OrderID	CustomerName	OrderDate
304101	Балалайка Сервис	10-05-2021
304102	null	20-06-2021
304103	Редиска Менеджмент	25-07-2021

RIGHT OUTER JOIN / RIGHT JOIN

В финальный результат этого соединения попадут все записи из правой, второй таблицы. Даже если не будет ни одного совпадения с левой. И записи из первой таблицы, для которых выполняется условие объединения.

Синтаксис:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Пример:

Таблица Orders:

OrderID	CustomerID	OrderDate
304101	21	10-05-2021
304102	34	20-06-2021
304103	22	25-07-2021

Таблица Customers:

CustomerID	CustomerName	ContactName
21	Балалайка Сервис	Иван Иванов
22	Рога и копыта	Семён Семёнов
23	Редиска Менеджмент	Пётр Петров

Запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
RIGHT JOIN Customers 
ON Orders.CustomerID=Customers.CustomerID;

Результат:

OrderID	CustomerName	OrderDate
304101	Балалайка Сервис	10-05-2021
null	Рога и копыта	null
304103	Редиска Менеджмент	25-07-2021

FULL OUTER JOIN / FULL JOIN

В финальный результат такого соединения попадут все записи из обеих таблиц. Независимо от того, выполняется условие объединения или нет.

Синтаксис:

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Пример:

Таблица Orders

OrderID	CustomerID	OrderDate
304101	21	10-05-2021
304102	34	20-06-2021
304103	22	25-07-2021

Таблица Customers:

CustomerID	CustomerName	ContactName
21	Балалайка Сервис	Иван Иванов
22	Рога и копыта	Семён Семёнов
23	Редиска Менеджмент	Пётр Петров

Запрос:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
FULL JOIN Customers 
ON Orders.CustomerID=Customers.CustomerID;

Результат:

OrderID	CustomerName	OrderDate
304101	Балалайка Сервис	10-05-2021
304102	null	20-06-2021
304103	Редиска Менеджмент	25-07-2021
null	Рога и копыта	null

Перекрестное соединение CROSS JOIN

Этот оператор отличается от предыдущих операторов соединения: ему не нужно задавать условие объединения (ON table1.column_name = table2.column_name). Записи в таблице с результатами — это результат объединения каждой записи из левой таблицы с записями из правой. Такое действие называют декартовым произведением.

Синтаксис:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Пример:

Таблица Orders:

OrderID	CustomerID	OrderDate
304101	21	10-05-2021
304102	34	20-06-2021
304103	22	25-07-2021

Таблица Customers:

CustomerID	CustomerName	ContactName
21	Балалайка Сервис	Иван Иванов
22	Рога и копыта	Семён Семёнов
23	Редиска Менеджмент	Пётр Петров

Запрос:

</p>
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
CROSS JOIN Customers;

Результат:

OrderID	CustomerName	OrderDate
304101	Балалайка Сервис	10-05-2021
304101	Рога и копыта	10-05-2021
304101	Редиска Менеджмент	10-05-2021
304102	Балалайка Сервис	20-06-2021
304102	Рога и копыта	20-06-2021
304102	Редиска Менеджмент	20-06-2021
304103	Балалайка Сервис	25-07-2021
304103	Рога и копыта	25-07-2021
304103	Редиска Менеджмент	25-07-2021

Соединение SELF JOIN

Его используют, когда в запросе нужно соединить несколько записей из одной и той же таблицы.

В SQL нет отдельного оператора, чтобы описать SELF JOIN соединения. Поэтому, чтобы описать соединения данных из одной и той же таблицы, воспользуйтесь операторами JOIN или WHERE.

Учтите, что в одном запросе нельзя дважды использовать имя одной и той же таблицы: иначе запрос вернет ошибку. Поэтому, чтобы выполнить соединение таблицы SQL с самой собой, в запросе ей присваивают два разных временных имени — алиаса.

Синтаксис соединения SELF JOIN при использовании оператора JOIN:

SELECT column_name(s)
FROM table1 a1
JOIN table1 a2
ON a1.column_name = a2.column_name;

Оператор JOIN может быть любым: используйте LEFT JOIN, RIGHT JOIN. Результат будет таким же, как когда объединяли две разные таблицы.

Синтаксис соединения SELF JOIN при использовании оператора WHERE:

SELECT column_name(s)
FROM table1 a1, table1 a2
WHERE a1.common_col_name = a2.common_col_name;

Пример:

Талица Students:

StudentID	Name	CourseID	Duration
1	Артём	1	3
2	Пётр	2	4
1	Артём	2	4
3	Борис	3	2
2	Ирина	3	5

Запрос с оператором WHERE:

SELECT  s1.StudentID, s1.Name
FROM Students AS s1, Students s2
WHERE s1.StudentID = s2.StudentID
AND s1.CourseID <> s2.CourseID;

Результат:

StudentID	Name
1	Артём
2	Ирина
1	Артём
2	Пётр

Запрос с оператором JOIN:

SELECT  s1.StudentID, s1.Name
FROM Students s1
JOIN Students s2
ON s1.StudentID = s2.StudentID
AND s1.CourseID <> s2.CourseID
GROUP BY StudentID;

Результат:

StudentID	Name
1	Артём
2	Ирина

Главное о join в SQL

• В SQL используют операторы соединения join, чтобы объединять данные из нескольких таблиц. Когда результат должен содержать только данные двух таблиц с общим ключом, применяют INNER JOIN или просто JOIN.
• Если нужен полный список записей одной из таблиц, объединенных с данными из другой, используют операторы LEFT и RIGHT JOIN.
• Если результат должен содержать полный список записей обеих таблиц, где некоторые записи объединены, применяют оператор FULL JOIN.
• Если нужно декартово произведение двух таблиц, используют оператор CROSS JOIN. Хотите соединить данные из одной и той же таблицы между собой — нужен SELF JOIN.

Научитесь писать SQL-запросы на курсе «Аналитик данных» Skypro. Изучите агрегатные функции, подзапросы и WITH, JOIN, оконные функции и многое другое, а еще — решите бизнес-задачу с помощью SQL.

Еще в программе: базовые формулы Excel, работа в Power Pivot и Power Query, Python для анализа данных. Справитесь и без опыта в IT: учим с азов, ведем до диплома и помогаем найти работу.

например, так.

• первый запрос — общие друзья для пользователей с идентификаторами 1 и 2;
• второй запрос — общие друзья для всех пар пользователей (у которых есть общие друзья).

SQL Fiddle

MySQL 5.6 Schema Setup:

create table u (id int, n text);

insert into u values
   (1, "вася")
  ,(2, "оля")
  ,(3, "юра")
  ,(4, "маша")
  ,(5, "борис")
  ,(6, "аня")
;

create table f (user int, friend int);

insert into f values
   (1, 3)
  ,(1, 4)
  ,(1, 5)
  ,(2, 3)
  ,(2, 4)
  ,(2, 6)
  ,(3, 4)
;

Query 1:

select f1.friend, u.n
from f f1
join f f2
  on f2.user = 2
  and f2.friend = f1.friend
join u
  on u.id = f1.friend
where f1.user = 1

Results:

| friend |    n |
|--------|------|
|      3 |  юра |
|      4 | маша |

Query 2:

select f1.user, f2.user, f1.friend, u.n
from f f1
join f f2
  on f2.friend = f1.friend
  and f2.user > f1.user
join u
  on u.id = f1.friend

Results:

| user | user | friend |    n |
|------|------|--------|------|
|    1 |    2 |      3 |  юра |
|    1 |    2 |      4 | маша |
|    1 |    3 |      4 | маша |
|    2 |    3 |      4 | маша |