SQL-Server-Tutorial/SQL Server Self Join.sql at main · krasigarev/SQL-Server-Tutorial · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SELECT
    e.first_name + ' ' + e.last_name AS employee,
    m.first_name + ' ' + m.last_name as manager
FROM sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY manager;
GO

SELECT
    e.first_name + ' ' + e.last_name AS employee,
    m.first_name + ' ' + m.last_name as manager
FROM sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY manager;
GO

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name CUSTOMER_1,
    c2.first_name + ' ' + c2.last_name CUSTOMER_2
FROM sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id AND c1.city = c2.city
ORDER BY city, CUSTOMER_1, CUSTOMER_2;
GO

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY city, customer_1, customer_2;
GO

SELECT
   customer_id, first_name + ' ' + last_name c,
   city
FROM sales.customers
WHERE city = 'Albany'
ORDER BY c;
GO

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY c1.city, customer_1, customer_2;
GO

SELECT
    c1.city,
	c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY c1.city, customer_1, customer_2;
GO