teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

2

追記

2020/11/02 09:20

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -12,4 +12,37 @@
12
12
  left JOIN HOLIDAY h3
13
13
  ON h3.holiday_code='3000'
14
14
  and p.person_code = h3.person_code
15
+ ```
16
+ 上記をイメージし易いような別な記述にすると、
17
+ ```SQL
18
+ SELECT p.person_name
19
+ , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
20
+ FROM PERSON p
21
+ left JOIN (
22
+ select * from HOLIDAY where holiday_code='1000'
23
+ ) h1
24
+ ON p.person_code = h1.person_code
25
+ left JOIN (
26
+ select * from HOLIDAY where holiday_code='2000'
27
+ ) h2
28
+ ON p.person_code = h2.person_code
29
+ left JOIN (
30
+ select * from HOLIDAY where holiday_code='3000'
31
+ ) h3
32
+ ON p.person_code = h3.person_code
33
+ ```
34
+ where条件で指定しようとすると、
35
+ ```SQL
36
+ SELECT p.person_name
37
+ , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
38
+ FROM PERSON p
39
+ left JOIN HOLIDAY h1
40
+ ON p.person_code = h1.person_code
41
+ left JOIN HOLIDAY h2
42
+ ON p.person_code = h2.person_code
43
+ left JOIN HOLIDAY h3
44
+ ON p.person_code = h3.person_code
45
+ where (h1.holiday_code='1000' or h1.holiday_code is null)
46
+ and (h2.holiday_code='2000' or h2.holiday_code is null)
47
+ and (h3.holiday_code='3000' or h3.holiday_code is null)
15
48
  ```

1

訂正

2020/11/02 09:20

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -7,9 +7,9 @@
7
7
  ON h1.holiday_code='1000'
8
8
  and p.person_code = h1.person_code
9
9
  left JOIN HOLIDAY h2
10
- ON h1.holiday_code='2000'
10
+ ON h2.holiday_code='2000'
11
11
  and p.person_code = h2.person_code
12
12
  left JOIN HOLIDAY h3
13
- ON h1.holiday_code='3000'
13
+ ON h3.holiday_code='3000'
14
14
  and p.person_code = h3.person_code
15
15
  ```