How to display mutiple values from multiple table using single id?

Question

How to get multiple values from multiple table in matching of Single Id using PostgreSQL query, here is the sample table.

CREATE TABLE emp_tbl(
emp_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
);
CREATE TABLE sal_tbl(
sal_id INT PRIMARY KEY NOT NULL,
emp_id INT NOT NULL,
dep_id INT NOT NULL,
sal VARCHAR(50)
);
CREATE TABLE dep_tbl(
dep_id INT PRIMARY KEY NOT NULL,
emp_id INT NOT NULL,
dep VARCHAR(50)
);

INSERT INTO emp_tbl(emp_id, name)
VALUES (10001,'name1');
INSERT INTO emp_tbl(emp_id, name)
VALUES (10002,'name2');
INSERT INTO emp_tbl(emp_id, name)
VALUES (10003,'name3');
INSERT INTO emp_tbl(emp_id, name)
VALUES (10004,'name4');

INSERT INTO sal_tbl(sal_id,emp_id, dep_id, sal)
VALUES (1, 10001,101,30000);
INSERT INTO sal_tbl(sal_id, emp_id, dep_id, sal)
VALUES (2, 10002,102,40000);
INSERT INTO sal_tbl(sal_id, emp_id, dep_id, sal)
VALUES (3, 10002,103,50000);
INSERT INTO sal_tbl(sal_id, emp_id, dep_id, sal)
VALUES (4, 10002,104,60000);

INSERT INTO dep_tbl(dep_id,emp_id, dep)
VALUES (101, 10001,"xxxx");
INSERT INTO dep_tbl(dep_id, emp_id, dep)
VALUES (102, 10002,"yyyy");
INSERT INTO dep_tbl(dep_id, emp_id, dep)
VALUES (103, 10002,"zzzz");
INSERT INTO dep_tbl(dep_id, emp_id, dep)
VALUES (104, 10002,"ssss");

Join emp_id for all other tables( sal_tbl, dep_tbl etc..)

Expected Response :

        emp_id   name     dep_id  dep.   sal.
       10001    name1     101      xxxx    10000    
       10002    name2     102      yyyy    40000
                          103      zzzz    50000
                          104      ssss     60000

Answer 1

We can write a query that produces the expected result, but I am not sure that we should.

To start with, the blank lines for emp_id is something which should be produced in the presentation layer. The query should return an emp_id for all rows.

Next, what says that yyyy should go with 40000 and zzzz with 50000? What is the business rules that makes these ties? As Guoxiong suggests, I think that there is a data-modelling problem here that I you need to address first.

Answer 2

Now when the data model has been cleaned up, this is a straightforward join:

SELECT e.emp_id, e.name, d.dep_id, d.dep, s.sal
FROM   emp_tbl e
JOIN   dep_tbl d ON e.emp_id = d.emp_id
JOIN   sal_tbl s ON s.emp_id = d.emp_id
                AND s.dep_id = d.dep_id;