2

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables:

enter image description here

The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost always included in the same waste water and drinking water perimeters. But a few ones can be included in a waste water perimeter different from the drinking water one.

Table town:

t_code  | t_waste_water | t_drinking_water
--------+---------------+------------------
 001    | 01            | 01
 002    | 02            | 02
 003    | 01            | 03
 004    | 02            | 02
 005    | 01            | 01

Table perimeter:

perim_code | perim_name
-----------+------------
 01        | North
 02        | West
 03        | East

For instance, town 003is included in the North perimeter for waste water and to the East perimeter for drinking water stuff. Perimeters having the same names for waste water or drinking water concerns, I have put them in a single central table, instead of duplicating informations into one waste water table and one drinking water table.

Now, I would like to get perimeters' names together with town code in a single table, as bellow:

town    | perim_waste_water | perim_drinking_water
--------+-------------------+------------------
 001    | North             | North
 002    | West              | West
 003    | North             | East

My problem is that I don't see how to display more than one perimeter name for I can specify one relation (JOIN) between the two tables. The following query is not usefull but is a start...

SELECT 
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  b.perim_name AS drinking_water_perim

FROM schema.town a
  LEFT JOIN schema.perimeter b ON a.t_waste_water = b.perim_code AND a.t_drinking_water = b.perim_code;

Is there a solution inside the query or do I have to structure data differently in the database?

1

I had to use two JOINs here but I think that this will work. You can join on the same table multiple times

SELECT 
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  c.perim_name AS drinking_water_perim

FROM schema.town a
  JOIN schema.perimeter b ON a.t_waste_water = b.perim_code 
  JOIN schema.perimeter c ON a.t_drinking_water = c.perim_code;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.