SQLパズル4 入館証　解法1　メモ

# 概要
SQLパズル4入館証 のp18 の 解法１の学習メモです。
テーマとしては、自己結合の使い方が肝です。


# 設問

・社員は現在働いてる職場に応じて、複数の入館証を持つことができる。
・同時に有効な入館証は一つだけ
・有効な入館証は　新しい職場で発行された入館証とする。


# SQL

## DDL
```sql
DROP TABLE IF EXISTS personnel CASCADE;
CREATE TABLE personnel
(
    emp_id integer PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL
);

Insert  Into personnel values
(1,'John'),
(2,'Mary'),
(3,'Bill'),
(4,'Mike')
;


DROP TABLE IF EXISTS badges CASCADE;
CREATE TABLE Badges
(badge_nbr INTEGER NOT NULL PRIMARY KEY,
 emp_id INTEGER NOT NULL,
 issued_date DATE NOT NULL,
 badge_status CHAR(1) NOT NULL
    CHECK (badge_status IN ('A', 'I')));



INSERT INTO Badges VALUES(100, 1, '2007-01-01', 'I');
INSERT INTO Badges VALUES(200, 1, '2007-02-01', 'I');  --社員1番の最新バッジ
INSERT INTO Badges VALUES(300, 2, '2007-03-01', 'I');  --社員2番の最新バッジ
INSERT INTO Badges VALUES(400, 3, '2007-03-01', 'I');  
INSERT INTO Badges VALUES(500, 3, '2007-04-01', 'I');  
INSERT INTO Badges VALUES(600, 3, '2007-05-01', 'I');  --社員3番の最新バッジ
INSERT INTO Badges VALUES(800, 4, '2007-07-01', 'I');  
INSERT INTO Badges VALUES(900, 4, '2007-08-01', 'A');  --社員4番の最新バッジ

```

## 答えのSQL

```sql
-- Update Badges
--     SET badge_status = 'A'
    SELECT * 
    FROM Badges
    WHERE ('I' = ALL (SELECT badge_status   --イの条件
                        FROM Badges AS B1
                        WHERE Badges.emp_id = B1.emp_id))
    AND (issued_date = (SELECT MAX(issued_date) -- ロの条件
                            FROM Badges AS B2
                        WHERE Badges.emp_id = B2.emp_id));

```

## 図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/dc3bac2f-8edb-9eda-0605-af5d9940c5a1.png)


# 分解して考える

## イ の条件
①にemp_idに対して、badge_statusの組み合わせを自己結合を使って求める。
②限定術後のALLを使って、全てのレコードがbadge_status='I'であるemp_idを割り出す。




### sql
```sql
-- ①組み合わせを求める
SELECT B1.*,B2.*
FROM Badges AS B1, Badges AS B2
WHERE B2.emp_id = B1.emp_id;

-- ②emp_idでB2の全てのbadge_statusが'I'のレコードのみを取得する。
SELECT B2.*
FROM Badges AS B2
WHERE
('I' = ALL (SELECT B2.badge_status
              FROM Badges AS B1
              WHERE B2.emp_id = B1.emp_id));
```

### 図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/d7638e70-98a3-036a-5ad7-84ec7d8d2ae6.png)



## ロ の条件

### sql
①にemp_idに対して、issued_dateの組み合わせを自己結合を使って求める。
②限定術後のALLを使って、全てのレコードがbadge_status='I'であるemp_idを割り出す。

```sql
-- ①emp_idに対して、issued_dateの組み合わせを求める
SELECT B1.emp_id,B1.issued_date
    FROM Badges AS B2,Badges AS B1
	WHERE B1.emp_id = B2.emp_id;

-- ②　①で求めたissued_dateの組み合わせの内最大値を求める。
SELECT * 
FROM Badges AS B1
WHERE
(B1.issued_date = (SELECT MAX(B2.issued_date)
                        FROM Badges AS B2
                        WHERE B1.emp_id = B2.emp_id));
```

### 図

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/0b143638-cc09-156a-eec6-110c5f0cfce0.png)

## イ　AND ロ

### SQL
```sql
SELECT * 
FROM Badges
WHERE
('I' = ALL (SELECT badge_status
              FROM Badges AS B1
              WHERE Badges.emp_id = B1.emp_id))
AND
(issued_date = (SELECT MAX(issued_date)
                            FROM Badges AS B2
                        WHERE Badges.emp_id = B2.emp_id));

```

### 図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/4b383183-6d3d-afec-ff4c-67cdac795270.png)

# 参考

SQLパズル　p18

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p4

達人に学ぶSQL徹底指南書第２版 p44　自己結合の使い方　組み合わせ
達人に学ぶSQL徹底指南書第２版 p53　まとめ参考書籍

https://github.com/RYA234/SQL_Puzzle_Learning/commit/5c6e7d091fc567a6760fc5d6a2b60a4860836d20