current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have four tables

TABLE_A
----------------
ID AName1 AName2
1  A11    A12
2  A21    A22
3  A31    A32

TABLE_B
------------
AID ID BName
1   B11 Ba11
1   B12 Ba12
1   B13 Ba13
1   B14 Ba14
2   B21 Ba21
2   B22 Ba22
3   B31 Ba31

TABLE_C
----------------
AID BID ID CName
1   B11 C11 Ca11
1   B11 C12 Ca12
1   B11 C13 Ca13
1   B12 C14 Ca14
1   B13 C15 Ca15
1   B13 C16 Ca16
1   B14 C17 Ca17
1   B14 C18 Ca18
1   B14 C19 Ca19
2   B21 C20 Ca20
2   B21 C21 Ca21
2   B22 C22 Ca22
2   B22 C23 Ca23
2   B22 C24 Ca24
2   B22 C25 Ca25

TABLE_D
----------------
AID BID ID DName
1   B11 D1  Da1
1   B11 D2  Da2
1   B11 D3  Da3
1   B12 D4  Da4
1   B12 D5  Da5
1   B13 D6  Da6
1   B13 D7  Da7
1   B13 D8  Da8
1   B13 D9  Da9
1   B14 D10 Da10
2   B21 D11 Da11
2   B21 D12 Da12
2   B21 D13 Da13
2   B22 D14 Da14
2   B22 D15 Da15

Given a single pair of AName1 and AName2, I would like to get unique values of BName and the corresponding arrays of CName and DName.

For example, given AName1 = A21 and AName2 = A22, I would like to get the following:

BName CName                    DName
B21   {Ca20, Ca21}             {Da11, Da12, Da13}
B22   {Ca22, Ca23, Ca24, Ca25} {Da14, Da15}

I'm currently using the following query:

SELECT
b.BName,
array_agg(c.CName),
array_agg(d.DName)
FROM
TABLE_B
INNER JOIN
TABLE_A on b.AID=TABLE_A.ID
LEFT JOIN
TABLE_C c on c.BID = b.ID 
LEFT JOIN
TABLE_D d on d.BID=b.ID
WHERE
TABLE_A.AName1 like 'A21' AND
TABLE_A.AName2 like 'A22'
GROUP BY
b.ID

This is repeating values as if it is also grouping on C and D:

BName CName                                DName
B21   {Ca20, Ca20, Ca20, Ca21, Ca21, Ca21} {Da11, Da12, Da13, Da11, Da12, Da13}

Does anyone know how I might be able to correct this issue? I'm using PostgreSQL version 9.2.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

SQL Fiddle

select
    b.bname,
    array_agg(distinct c.cname),
    array_agg(distinct d.dname)
from
    table_b b
    inner join
    table_a a on b.aid = a.id
    left join
    table_c c on c.bid = b.id
    left join
    table_d d on d.bid = b.id
where
    a.aname1 = 'A21' and
    a.aname2 = 'A22'
group by b.bname
share|improve this answer
    
Perfect, thanks! –  abe678 Nov 7 '14 at 13:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.