CODEWITHSUNDEEP
sqlpostgresql
Pyrite
Pyrite

Reputation: 287

SQL Query Help (Postgresql)

I'm having trouble wrapping my head around a query. I have the following 3 tables:

documents (
    id,
    title
);

positions (
    id,
    title
);

documents_positions (
    document_id,
    position_id
);

What I am trying to get (my requested outcome) is a matrix of documents, and what positions they apply to. So each row would have document title, and then have a column for every position and a column next to it with True or False if the positions applies to the document. I suspect some kind of LEFT JOIN is required, because on each row after document, I want to list every position from the positions table and whether or not the document applies to it. Make sense?

Upvotes: 2

Views: 138

Answers (2)

ain
ain

Reputation: 22749

Since you want to turn positions rows into columns you have to "pivot" them. In PostgreSQL this is done with crosstab function. However, crosstab seems to require that you define the number of output columns in the query which you can't do as the number of rows in the positions is subject to change? I'm not a PostgreSQL user myself so perhaps there is some trick to build the dynamic query I don't know of but it seems to be easier to use query like Andomar posted and then pivot the rows in your client code...

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You could use a cross join to build the matrix, and then left join to find the positions in the matrix that are filled:

select  d.title
,       p.title
,       case when dp.document_id is null then 'hrmm' else 'HALLELUJAH' end
from    documents d
cross join
        positions p
left join
        documents_positions dp
on      dp.document_id = d.id
        and dp.position_id = p.id

Upvotes: 1

Related Questions