Visualize SQL | Find The Last Two Salaries(part 3) | LEFT JOIN and MAX

# Beginning
This technique is based on SQL Puzzle and Answers # 15 Find The Last Two Salaries
It is better with 『SQL Puzzle and Answers』 to read this article.

Because it says the idea of algorithm and
You can check what each quiz is intended for ・・・

Let's start !!

Employee name, promotion date and salary are added in one line
Some employees have been promoted a few times and Someone has not
　⇒　check the Salaries table


The difference from 『Part 2』is that current promotion is taken out first
then join the necessary information to the current promotion using LEFT JOIN
Since it uses LEFT JOIN three times, It is a good quiz to learn LEFT JOIN

`This quiz is to get current and previous salary information on one line`
The format to display is below

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d78e2682-2beb-3270-57f6-714ca9f960de.png)

**I have confirmed that it works with PostgreSQL**

# Salaries table
```SQL:SQL
CREATE TABLE Salaries (
	emp_name CHAR(10) NOT NULL,
	sal_date DATE NOT NULL,
	sal_amt DECIMAL(8,2) NOT NULL,
	PRIMARY KEY (emp_name, sal_date)
)

INSERT INTO Salaries
VALUES ('Tom',   '1996-06-20', 500.00),
       ('Tom',   '1996-08-20', 700.00),
       ('Tom',   '1996-10-20', 800.00),
       ('Tom',   '1996-12-20', 900.00),
       ('Dick',  '1996-06-20', 500.00),
       ('Harry', '1996-07-20', 500.00),
       ('Harry', '1996-09-20', 700.00);
```

▼ Output

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e48a01c4-8b94-33b5-4615-a5b4ff605d78.png)


# Taking out current promotion
```SQL:SQL
SELECT W.emp_name, MAX(W.sal_date) AS maxdate
FROM Salaries AS W
GROUP BY W.emp_name
```

▼ Output

|emp_name|maxdate|
|---|---|
|Tom|1996-12-20|
|Harry|1996-09-20|
|Dick|1996-06-20|


# Join All Previous Promotions
```SQL:SQL
SELECT A.emp_name, A.maxdate, X.sal_date
FROM 
(
   SELECT W.emp_name, MAX(W.sal_date) AS maxdate
   FROM Salaries AS W
   GROUP BY W.emp_name
) AS A
LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
```

▼ Output(left said of image)

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ae98789a-ab34-fa83-fe61-2f005e970b64.png)

LEFT JOIN Salaries table to the output of the current promotion
join conditions are ・・・
　A.emp_name = X.emp_name　<-- the same worker's name
　A.maxdate > X.sal_date　　　<-- date before current promotion(maxdate)


Tom's case ・・・
There are three promotions before 1996-12-20 such as 1996-06-20,1996-08-20,1996-10-20
**That is why 『data count』 after LEFT JOIN goes up to three**


# Taking Out Previous Promotion
```SQL:SQL
SELECT A.emp_name, A.maxdate, MAX(X.sal_date) AS maxdate2
FROM
(
   SELECT W.emp_name, MAX(W.sal_date) AS maxdate
   FROM Salaries AS W
   GROUP BY W.emp_name
) AS A
LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
GROUP BY A.emp_name, A.maxdate
```

▼ Output(right side of image)

|emp_name|maxdate|maxdate2|
|---|---|---|
|Tom|1996-12-20|1996-10-20|
|Harry|1996-09-20|1996-07-20|
|Dick|1996-06-20|empty|


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5aacc83f-dffc-5451-75cc-ad2167038d7d.png)


# Answer SQL
```SQL:SQL
SELECT B.emp_name, B.maxdate, Y.sal_amt, B.maxdate2, Z.sal_amt
FROM
(
   SELECT A.emp_name, A.maxdate, MAX(X.sal_date) AS maxdate2
   FROM
   (
      SELECT W.emp_name, MAX(W.sal_date) AS maxdate
      FROM Salaries AS W
      GROUP BY W.emp_name
   ) AS A
   LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
   GROUP BY A.emp_name, A.maxdate
) AS B
LEFT JOIN Salaries AS Y ON B.emp_name = Y.emp_name AND B.maxdate = Y.sal_date
LEFT JOIN Salaries AS Z ON B.emp_name = Z.emp_name AND B.maxdate2 = Z.sal_date
```

▼ Output

|emp_name|B.maxdate|Y.sal_amt|B.maxdate2|Z.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|
|Dick|1996-06-20|500|empty|empty|


The current promotion and the previous promotion are taken
No current and previous salaries are taken yet. 
So we are going to get those salaries from Salaries table using LEFT JOIN

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/edf97120-26fe-6033-bc39-0e675469e10f.png)


# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)