question

muhammedarif-5449 avatar image
0 Votes"
muhammedarif-5449 asked muhammedarif-5449 commented

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

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

azure-database-postgresql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Your expected results do not match your sample data. For emp_id = 10002, the name is "name2". It is not possible you can get both "name2" and "name3".

0 Votes 0 ·

Sorry, I updated response.

0 Votes 0 ·

It seems you are missing the column dep_id in the table sal_tbl since one emp could have more than one dep, for example emp_id = 10002. So you cannot know which sal belongs to which emp with id of 10002.

0 Votes 0 ·

I added dep_id in the response.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered muhammedarif-5449 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I updated response based on the id's.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered muhammedarif-5449 commented

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;

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Expected response is not getting using this query.

0 Votes 0 ·

Expected response is not getting using this query. can You check my previous question https://docs.microsoft.com/en-us/answers/questions/469608/how-to-join-multiple-table-getting-mutiple-values.html

same, but i need to join more tables with multiple values using single id, not bother correct values row by row, just need to show the values.

0 Votes 0 ·

The only difference I can see is that the first row has 10000 in expected output, but my query has 30000. But since there is mo row with 10000 in the source data, what could a poor boy to.

If you are alluding to that you have left the emp_id blank in the last two rows, I discussed in my first post. This is something you handle in the presentation layer. You never to that in the query itself.

0 Votes 0 ·
Show more comments