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

it produces an error because you are using the tablename after defining an alias..in `select`. — Vamsi Prabhala, Sep 06 '17 at 16:22
Instead of INNER JOIN, use LEFT JOIN on both. Then in your SELECT, reference the org1 and org2 columns you need. — Shawn, Sep 06 '17 at 16:24
Thank you both [Shawn](https://stackoverflow.com/users/1970497/shawn) and [Juergen](https://stackoverflow.com/users/575376/juergen-d). Now I actually understand the difference between an inner and left join, and also that left and left outer joins are the same thing. — SteveSong, Sep 06 '17 at 16:40
https://www.techonthenet.com/sql_server/joins.php <<< That is T-SQL, but this is pretty generic SQL, and the link puts the JOIN types into a graphical presentation that is very easy to understand. — Shawn, Sep 06 '17 at 17:00

score 1 · Answer 1 · answered Sep 06 '17 at 16:25

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

score 1 · Accepted Answer · answered Sep 06 '17 at 16:32

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

answered Sep 06 '17 at 16:32

Shawn

4,758
1
20
29

NOTE: If your fibrephase doesn't have an owner or an operator, a NULL will show up in the place of those. If you want to eliminate those that don't have both an owner and an operator, you can go back to the INNER JOINs. – Shawn Sep 06 '17 at 16:35

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

2 Answers2