CODEWITHSUNDEEP
databasepostgresqlleft-join
Qstacker
Qstacker

Reputation: 255

How to do left outer join on columns containing array.

I have something like this.

\d Table1 

uid |int (primary key)
name|text
cids|int[]

example: 

id   | name  | cids 
 1   | joe   |{10,20,30}
 2   | marry |{10,20,30,40}
 3   | john  |{30,40}

\d Table2

cid   |int (primary key)
name  |text
type  |int

example 

cid  | name  |  type
10   | aname | 0  
20   | bname | 0
30   | cname | 1
40   | dname | 0

Now i wanted a result like id, name from table1 and cid , name from table 2. where type !=1 

Example. 
id | name | cid      |cnames
1  | Joe | {10,20}   |{aname,bname}

Please tell me what is the efficient way to do this. Thanks in advance.

Upvotes: 0

Views: 1231

Answers (1)

rs.
rs.

Reputation: 27467

Try this For Versions < 9.0 

SELECT A.id, A.name, 
array_agg(b.cid) cids,array_agg(B.name) cnames
FROM Table1 A
JOIN 
(
  SELECT * FROM Table2 ORDER BY cid
)
B ON B.cid = ANY(A.cids)
WHERE type <> 1
GROUP BY A.id, A.name;

(Following Works for version 9+)

SELECT A.id, A.name, 
array_agg(b.cid order by b.cid) cids,
array_agg(B.name ORDER BY B.name) cnames
FROM Table1 A
JOIN Table2 B ON B.cid = ANY(A.cids)
WHERE type <> 1
GROUP BY A.id, A.name

SQL DEMO HERE

Upvotes: 1

Related Questions