CODEWITHSUNDEEP
sqlpostgresql
t56k
t56k

Reputation: 7001

PostgreSQL: get all children from a join table

I've got a query that looks like this:

SELECT u.name, d.title, c.name
FROM documents d 
INNER JOIN publications p ON p.document_id = d.id
INNER JOIN components c ON p.component_id = c.id
INNER JOIN users u ON d.user_id = u.id

My problem is that this only returns the first c.name when documents can have many components through publications. I need something to iterate through each document and get all their publications, then fetch the title through each publication's component.

I've tried a with statement like so:

WITH child_publications AS (
    SELECT c.name, c.id
    FROM publications p
    INNER JOIN components c ON p.component_id = c.id
)

But I'm not quite certain how to wield them. Publications looks like this:

    Column    |  Type   |                         Modifiers                         | Storage | Stats target | Description 
--------------+---------+-----------------------------------------------------------+---------+--------------+-------------
 id           | integer | not null default nextval('publications_id_seq'::regclass) | plain   |              | 
 document_id  | integer |                                                           | plain   |              | 
 component_id | integer |                                                           | plain   |              |

Components like this:

            Column             |            Type             |                        Modifiers                        | Storage  | Stats target | Description 
-------------------------------+-----------------------------+---------------------------------------------------------+----------+--------------+-------------
 id                            | integer                     | not null default nextval('components_id_seq'::regclass) | plain    |              | 
 name                          | character varying           |                                                         | extended |              | 
 body                          | text                        |                                                         | extended |              |

Documents like this:

        Column         |            Type             |                       Modifiers                        | Storage  | Stats target | Description 
-----------------------+-----------------------------+--------------------------------------------------------+----------+--------------+-------------
 id                    | integer                     | not null default nextval('documents_id_seq'::regclass) | plain    |              | 
 message               | text                        |                                                        | extended |              | 
 created_at            | timestamp without time zone | not null                                               | plain    |              | 
 updated_at            | timestamp without time zone | not null                                               | plain    |              | 
 title                 | character varying           |                                                        | extended |              | 
 user_id               | integer                     | not null                                               | plain    |              |

The column names are representative of what data they hold, I'm not sure what DDL is.

Upvotes: 0

Views: 866

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

INNER JOINS restrict the result to only those rows that meet ALL join conditions. An OUTER JOIN removes that and allows ALL ROWS FROM documents (in this query). The most common outer join is the LEFT OUTER JOIN as used below:

SELECT u.name, d.title, c.name
FROM documents d 
LEFT OUTER JOIN publications p ON p.document_id = d.id
LEFT OUTER JOIN components c ON p.component_id = c.id
LEFT OUTER JOIN users u ON d.user_id = u.id

The "downside" to this is you may get NULLs in the result now.

"OUTER" is optional in SQL syntax, so you could use the following, and it would be the same result:

SELECT u.name, d.title, c.name
FROM documents d 
LEFT JOIN publications p ON p.document_id = d.id
LEFT JOIN components c ON p.component_id = c.id
LEFT JOIN users u ON d.user_id = u.id

Upvotes: 1

Related Questions