How to join multiple table (getting mutiple values like array) using single ID?

Question

How to join multiple table (getting mutiple values like array) using single ID?

How to get multiple values for same ID in row by row for a join multiple table using single ID. Here is sample,
DB Postgresql.
Table 1 :emp_tbl

ID   dep_id  name             dept. 

1    10001   name1            xxxx  
2    10001   name2            yyyy          
3    10002   name3            zzzz
4    10003   name4            zzzz
5    10004   name5            ssss

Table 2: sal_tbl

       ID   emp_id         sal.

        1    10001        10000
        2    10002        20000
        3    10003        30000
        4    10004        40000

Need to join two tables using query, and get below response

Response:

   ID     emp_id   name      dept.   sal.

    1      10001    name1     xxxx    10000
                    name2     yyyy     
    2      10002    name3     zzzz    20000
    3      10003    name4     zzzz    30000
    4      10004    name5     ssss    40000

Answer 1

Viorel 89,136

Try something like this:

; with Q as
(
    select *, 
    row_number() over (partition by dep_id order by ID ) n
    from emp_tbl
)
select
    iif(n=1, cast(s.ID as varchar(max)), '') ID,
    iif(n=1, cast(Q.dep_id as varchar(max)), '') emp_id,
    Q.[name],
    Q.[dept.],
    iif(n=1, cast(s.[sal.] as varchar(max)), '') [sal.]
from Q
inner join sal_tbl s on s.emp_id = Q.dep_id
order by Q.ID, n

muhammed arif 1 Reputation point

2021-07-09T11:46:27.25+00:00

I am expecting PostgreSQL query for this response.

Viorel 89,136

For new expectation, check this query:

with Q as
(
    select *, 
    row_number() over (partition by dep_id order by ID ) n
    from emp_tbl
)
select
    case when n=1 then cast(s.ID as varchar(73)) else '' end ID,
    case when n=1 then cast(Q.dep_id as varchar(73)) else '' end emp_id,
    Q."name",
    Q."dept.",
    case when n=1 then cast(s."sal." as varchar(73)) else '' end "sal."
from Q
inner join sal_tbl s on s.emp_id = Q.dep_id
order by Q.ID, n;

muhammed arif 1 Reputation point

2021-07-09T14:06:52.457+00:00

Thanks, it's working for me.
muhammed arif 1 Reputation point

2021-07-10T17:51:45.73+00:00

I got this error when i run in SQL Editor, SQL Error [42601]ERROR: syntax error at or near "case" Position: 1
Viorel 89,136 Reputation points

2021-07-12T07:23:49.217+00:00

Show the tried query, if you modified the example.
muhammed arif 1 Reputation point

2021-07-13T11:02:14.383+00:00

It's working, some space issues in the query.
muhammed arif 1 Reputation point

2021-07-13T12:59:47.197+00:00

Is it possible for add multiple values 5 table same like this
Viorel 89,136 Reputation points

2021-07-13T13:52:12.527+00:00

If you have several tables that looks like emp_tbl, then you can use a UNION ALL to combine the rows. If your case is different, then give more details. (You can also start a new question with enough details, because the comment size seems limited here).
muhammed arif 1 Reputation point

2021-07-13T14:43:10.543+00:00

Ok, I asked new question for this https://learn.microsoft.com/en-us/answers/questions/474144/how-to-display-mutiple-values-from-multiple-table.html
muhammed arif 1 Reputation point

2021-07-15T07:01:04.503+00:00

Can You share UNION ALL sample query for several tables join in a Single id(emp_id). Every tables have a emp_id for join.

How to join multiple table (getting mutiple values like array) using single ID?

1 answer

Activity