CODEWITHSUNDEEP
sqlpostgresqljoin
bli00
bli00

Reputation: 2787

SQL RIGHT JOIN query to include all children?

I have tables A, B, and a A_B_MEMBERS table that links A and B. A can have multiple B, and B can have multiple A. Consider the following concrete table schemas:

CREATE TABLE A (
    a_id INT PRIMARY KEY,
    data TEXT
)

CREATE TABLE B (
    b_id INT PRIMARY KEY,
    data TEXT
)

CREATE TABLE A_B_MEMBERS (
    a_id NOT NULL REFERENCES A(a_id),
    b_id NOT NULL REFERENCES B(b_id),
    PRIMARY KEY (a_id, b_id)
)

Given a b_id, I want to query for all As that have this b_id but also return all other b_ids this A contains. So consider the following example:

a_id | data
-----+-----
1    | "hi"
2    | "hello"
3    | "cao"

b_id | data
-----+-----
1    | "people"
2    | "world"
3    | "foo"

a_id | b_id
-----+-----
1    | 1
1    | 2
1    | 3
2    | 1
2    | 2
3    | 3

So a_id=1 has b_id={1,2,3}, a_id=2 has b_id={1,2}, a_id=3 has b_id={3}.

Given, say b_id=2, the return should be:

a_id |   data   | b_id_arr
-----+----------+---------
1    | "hi"     | {1,2,3}
2    | "hello"  | {1,2}

I'm unable to do this through table joins and is forced to do two separate queries. I've tried the following:

SELECT
a.*,
ARRAY_AGG(abm.b_id) b_id_arr
FROM A a
RIGHT JOIN A_B_MEMBERS abm
ON a.a_id = abm.a_id
AND abm.b_id = 2
GROUP by a.a_id;

But this doesn't seem to give me what I'd want.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Use left join -- the logic is just easier to follow: keep all rows in the first table you see in the from clause.

That said, you don't seem to need an outer join. You simply want to groups that have a "2". That suggests a HAVING clause:

SELECT a.*, ARRAY_AGG(abm.b_id)
FROM A a JOIN
     A_B_MEMBERS abm
     ON a.a_id = abm.a_id
GROUP by a.a_id
HAVING COUNT(*) FILTER (WHERE abm.b_id = 2) > 0

Upvotes: 2

Related Questions