Output of two tables in SQL

Question

I'm learning SQL and I came across the below-mentioned scenario.

Table 1
id  Name City
1   Ryan Chennai
2   Tom  Banglore
3   Sam  Pune
4   Kat  Mumbai

Table 2
id month salary
1  prev  1000
1  cur   4000
1  next  3000
2  prev  1000
2  cur   3000
2  next  4000
3  prev  2000
3  cur   5000
3  next  6000
4  prev  1500
4  cur   2500
4  next  3000

Output table should look like this

id  name city      cur  prev next
1   Ryan Chennai   4000 1000 3000
2
3
4

(Similarly for other ids as well.)

So what are the different ways to achieve this and also it would be very helpful if someone helps me to understand how to approach these kinds of problems.

You really don't need to use Lead or lag. This can be done using three SUM(CASE...) statements and a group by. — Ross Bush, Jun 01 '21 at 17:50

score 1 · Accepted Answer · answered Jun 01 '21 at 18:15

First we create the data.

DROP TABLE IF EXISTS table_1;
DROP TABLE IF EXISTS table_2;

CREATE TABLE table_1 (
  id INT PRIMARY KEY,
  Name VARCHAR(10),
  City VARCHAR(10)
);

CREATE TABLE table_2 (
    id INT,
    month VARCHAR(10),
    salary INT);

INSERT INTO table_1
VALUES (1, 'Ryan', 'Chennai'),
       (2, 'Tom', 'Banglore'),
       (3, 'Sam', 'Pune'),
       (4, 'Kat', 'Mumbai');

INSERT INTO table_2
VALUES (1, 'prev', 1000),
       (1, 'cur', 4000),
       (1, 'next', 3000),
       (2, 'prev', 1000),
       (2, 'cur', 3000),
       (2, 'next', 4000),
       (3, 'prev', 2000),
       (3, 'cur', 5000),
       (3, 'next', 6000),
       (4, 'prev', 1500),
       (4, 'cur', 2500),
       (4, 'next', 3000);

In order to achieve the desired result, we join the tables on the id column and sum over the month column, taking only the values into account which are needed, respectively.

SELECT t1.name
     , t1.city
     , SUM(CASE WHEN t2.month = 'cur' THEN salary ELSE 0 END) AS 'cur'
     , SUM(CASE WHEN t2.month = 'prev' THEN salary ELSE 0 END) AS 'prev'
     , SUM(CASE WHEN t2.month = 'next' THEN salary ELSE 0 END) AS 'next'
  FROM table_1 AS t1
       INNER JOIN table_2 AS t2 
                  USING (id)
 GROUP BY t1.name, t1.city;

We obtain the following table:

name	city	cur	prev	next
Ryan	Chennai	4000	1000	3000
Tom	Banglore	3000	1000	4000
Sam	Pune	5000	2000	6000
Kat	Mumbai	2500	1500	3000

Output of two tables in SQL

1 Answers1