How to query a postgresql table twice in a single SQL statement

Question

I have two tables.

af_fibrephase is a list of fibre optic network projects in Africa and consists of the key fields:

• the_geom <- map of the fibre network
• operator_id <- a unique id for the network operator
• owner_id <- a unique id for the network owner

af_organisation is a table that provides more detailed information about the organisations referenced in af_fibrephase. I have used a single table for both operator and owner organisations because they are sometimes the same thing and sometimes not. Key fields for af_organisation are:

• organisation_id <- key reference field
• name <- organisation name
• web_url <- url of organisations
• etc

I would like to make a query which pulls up the project from af_fibrephase and populates the details about both the owner and the operator from the organisation table. Looking through examples, it seems like something like the following should work but it produces a join error.

SELECT af_fibrephase.* 
FROM af_fibrephase AS fibre
INNER JOIN af_organisation AS org1 on fibre.operator_id = org1.organisation_id
INNER JOIN af_organisation AS org2 on fibre.owner_id = org2.organisation_id

Actual tables can be found at af_fibrephase and af_organisation on https://carto.com

Answer 1

See above comment. Use LEFT OUTER JOIN and alias needed fields from af_organisation.

SELECT fibre.*
    , org1.name AS operator_name
    , org1.web_url AS operator_web_url
    , org1.<etc> AS operator_<etc>
    , org2.name AS owner_name
    , org2.web_url AS owner_web_url
    , org2.<etc> AS owner_<etc>
FROM af_fibrephase AS fibre
LEFT OUTER JOIN af_organisation org1 on fibre.operator_id = org1.organisation_id
LEFT OUTER JOIN af_organisation org2 on fibre.owner_id = org2.organisation_id

Answer 2

SELECT fibre.*, 
       org1.name as org1_name,
       org2.name as org2_name
FROM af_fibrephase AS fibre
LEFT JOIN af_organisation AS org1 on fibre.operator_id = org1.organisation_id
LEFT JOIN af_organisation AS org2 on fibre.owner_id = org2.organisation_id