question

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

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












azure-database-postgresql
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.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered muhammedarif-5449 edited

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


· 10
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 am expecting PostgreSQL query for this response.

0 Votes 0 ·

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;


1 Vote 1 ·

Thanks, it's working for me.

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments

Is it possible for add multiple values 5 table same like this

0 Votes 0 ·
Show more comments