回答編集履歴
1
ちょうせい
answer
CHANGED
|
@@ -27,4 +27,45 @@
|
|
|
27
27
|
}
|
|
28
28
|
```
|
|
29
29
|
|
|
30
|
+
# リレーション関連
|
|
31
|
+
```SQL
|
|
32
|
+
create table customers(
|
|
33
|
+
id varchar(30) primary key not null,
|
|
34
|
+
last_name varchar(10),
|
|
35
|
+
first_name varchar(10),
|
|
36
|
+
email varchar(50),
|
|
37
|
+
created_at datetime);
|
|
30
|
-
|
|
38
|
+
insert into customers values
|
|
39
|
+
('cus_G50Aw9QPAn5VjF','田中','かずお','ken@gmail.com','2019-10-29 17:34:27'),
|
|
40
|
+
('cus_G50esjtjdXCVSX','吉田','たろう','yoshida@gmail.com','2019-10-29 18:04:45'),
|
|
41
|
+
('cus_G50ImEgofoHIjr','山田','こうすけ','goo@gmail.com','2019-10-29 17:42:48'),
|
|
42
|
+
('cus_G50VgUaw2V2CZo','藤田','ニコル','show@gmail.com','2019-10-29 17:56:22');
|
|
43
|
+
|
|
44
|
+
create table transactions(
|
|
45
|
+
id varchar(30) primary key not null,
|
|
46
|
+
customer_id varchar(30),
|
|
47
|
+
subscription varchar(10),
|
|
48
|
+
amount int,
|
|
49
|
+
currentcy varchar(10),
|
|
50
|
+
status varchar(10),
|
|
51
|
+
create_at datetime);
|
|
52
|
+
insert into transactions values
|
|
53
|
+
('sub_G50eORaCOnTnJB','cus_G50esjtjdXCVSX','monthly','15000','jpy','active','2019-10-29 18:04:45'),
|
|
54
|
+
('sub_G50IlV2zUFSPNh','cus_G50ImEgofoHIjr','monthly','15000','jpy','active','2019-10-29 17:42:48'),
|
|
55
|
+
('sub_G50WH4OU89NMip','cus_G50VgUaw2V2CZo','monthly','15000','jpy','active','2019-10-29 17:56:22');
|
|
56
|
+
|
|
57
|
+
```
|
|
58
|
+
- ken@gmail.com
|
|
59
|
+
```SQL
|
|
60
|
+
SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = 'ken@gmail.com';
|
|
61
|
+
```
|
|
62
|
+
※結果：ヒットなし
|
|
63
|
+
- yoshida@gmail.com
|
|
64
|
+
```SQL
|
|
65
|
+
SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = 'yoshida@gmail.com';
|
|
66
|
+
```
|
|
67
|
+
|
|
68
|
+
※結果
|
|
69
|
+
|email|id|
|
|
70
|
+
|:--|:--|
|
|
71
|
+
|yoshida@gmail.com|sub_G50eORaCOnTnJB|
|