CODEWITHSUNDEEP
sqlpostgresql
Eric G
Eric G

Reputation: 926

Find referenced value of multiple columns

I have a table Setpoints which contains 3 columns Base,Effective and Actual which contains an id that refers to the item found in io.

I would like to make a query that will return the io_value found in the io table for the id found in Setpoints.

Currently my query will return multiple id's and then I query the io table to find the io_value for each id.

Ex Query returning the ID's in the row

row #   |  base  | effective  | actual
  1     |  24    |   30       | 40
  2     |  25    |   31       | 41
  3     |  26    |   32       | 42

But i want it return the value instead of the id

Ex returning the value for the id's instead

row #   |  base  | effective  | actual
  1     |  2.3   |   4.5      |  3.44
  2     |  4.2   |   7.7      |  4.41
  3     |  3.9   |   8.12     |  5.42

Here are the table fields

IO
io_value
io_id

Setpoints
stpt_base
stpt_effective
stpt_actual

Using postgres 9.5

What Im using now

SELECT * from setpoints
For each row
     SELECT io_id, io_value
     from io
     where io_id in
     (stpt_effective, stpt_actual, stpt_base); 
     // these are from previous query

Upvotes: 1

Views: 24

Answers (1)

Patrick
Patrick

Reputation: 32244

You can solve this by joining the io table three times to the setpoints table, using the three columns in each individual JOIN:

SELECT a.io_value AS base,
       b.io_value AS effective,
       c.io_value AS actual
FROM setpoints s
JOIN io a ON a.io_id = s.stpt_base
JOIN io b ON b.io_id = s.stpt_effective
JOIN io c ON c.io_id = s.stpt_actual;

Upvotes: 1

Related Questions