-
Notifications
You must be signed in to change notification settings - Fork 1
/
SQL06-Join
85 lines (74 loc) · 3.17 KB
/
SQL06-Join
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
/* JOIN */
SELECT name, email, idclient
from client;
+------+----------------+----------+
| name | email | idclient |
+------+----------------+----------+
| john | john@gmail.com | 11 |
| mary | mary@gmail.com | 12 |
| dave | dave@gmail.com | 13 |
| ryan | ryan@gmail.com | 14 |
| caty | caty@gmail.com | 15 |
| bety | bety@gmail.com | 16 |
| kely | kely@gmail.com | 17 |
| noah | noah@gmail.com | 18 |
| tony | tony@gmail.com | 19 |
+------+----------------+----------+
SELECT id_Client, city, country
from adress;
+-----------+------------+---------+
| id_Client | city | country |
+-----------+------------+---------+
| 11 | California | USA |
| 12 | California | USA |
| 13 | California | USA |
| 14 | California | USA |
| 15 | California | USA |
| 16 | California | USA |
| 17 | California | USA |
| 18 | California | USA |
| 19 | California | USA |
+-----------+------------+---------+
-- inner join -> 2 tables
select UPPER(name) as 'client', sex, email, city, country
from client
inner join adress
on idClient = id_Client;
+--------+-----+----------------+------------+---------+
| client | sex | email | city | country |
+--------+-----+----------------+------------+---------+
| JOHN | M | john@gmail.com | California | USA |
| MARY | F | mary@gmail.com | California | USA |
| DAVE | M | dave@gmail.com | California | USA |
| RYAN | M | ryan@gmail.com | California | USA |
| CATY | F | caty@gmail.com | California | USA |
| BETY | F | bety@gmail.com | California | USA |
| KELY | M | kely@gmail.com | California | USA |
| NOAH | M | noah@gmail.com | California | USA |
| TONY | M | tony@gmail.com | California | USA |
+--------+-----+----------------+------------+---------+
select upper(name) as 'Name',email, kind, number
from client
inner join phone
on idClient = id_Client;
-- inner join -> 3 tables and same name on column using cursor
select UPPER(c.name) as Name, c.sex, d.city, d.country, e.kind, e.number
from client c -- c pointer to client table
inner join adress d -- d pointer to adress table
on c.idClient = d.id_Client
inner join phone e -- e pointer to phone table
on c.idClient = e.id_Client;
+------+-----+------------+---------+--------+-------------+
| Name | sex | city | country | kind | number |
+------+-----+------------+---------+--------+-------------+
| JOHN | M | California | USA | Mobile | 111111111 |
| JOHN | M | California | USA | House | 22222222222 |
| JOHN | M | California | USA | Office | 9999999 |
| MARY | F | California | USA | House | 194943 |
| DAVE | M | California | USA | Other | 109499 |
| RYAN | M | California | USA | Mobile | 954973 |
| RYAN | M | California | USA | House | 318494849 |
| CATY | F | California | USA | Office | 166194 |
| BETY | F | California | USA | House | 64161 |
| KELY | M | California | USA | Other | 1797821 |
+------+-----+------------+---------+--------+-------------+