CODEWITHSUNDEEP
sqlpostgresqlselectmultiple-columns
Krisselack
Krisselack

Reputation: 511

sql query: column by id multiple times

SQL newb here. I am having troubles to create a select statement.

I have one table REF with an id and a value v; 

+-----+----+
| REF |    |
+-----+----+
| ID  |  V |
| 1   | 10 |
| 2   | 20 |
| 3   | 30 |
| 4   | 40 |
| 5   | 50 |
| 6   | 60 |
| 7   | 70 |
+-----+----+

in a second table IND i have three columns with id-values; 

+-----+-----+-----+
|IND  |     |     |
|-----|-----|-----|
| ID1 | ID2 | ID3 |
| 1   | 2   |     |
|     | 3   | 4   |
| 5   |     |     |
| 6   |     | 7   |

I want to receive the value of REF.v accordinlgly in each of the three columns (Table RES); the ids are distinct, there is no overlapping in between. 

+-----+-----+-----+
| RES |     |     |
+-----+-----+-----+
| ID1 | ID2 | ID3 |
| 10  | 20  |     |
|     | 30  | 40  |
| 50  |     |     |
| 60  |     | 70  |
+-----+-----+-----+

Upvotes: 0

Views: 363

Answers (2)

Joe Taras
Joe Taras

Reputation: 15379

You can query your IND table, using three subqueries to retrieve the value linked to used id

Try this:

SELECT
    (SELECT V FROM REF WHERE REF.ID = IND.ID1) AS V_ID1,
    (SELECT V FROM REF WHERE REF.ID = IND.ID2) AS V_ID2,
    (SELECT V FROM REF WHERE REF.ID = IND.ID3) AS V_ID3
FROM IND

UPDATE

About two proposed solutions (LEFT OUTER JOINs Vs SUBQUERIES)

I've tried on Sql Fiddle under Postgres 9.6 engine.

Times are the same but execution plan about LEFT OUTER JOIN is more complex

You can see SqlFiddle SUBQUERIES and SqlFiddle LEFT OUTER JOINs

Upvotes: 3

Andrew
Andrew

Reputation: 1596

SELECT ref1.V AS RefV1, ref2.V AS RefV2, ref3.V AS RefV3
FROM IND i 
LEFT OUTER JOIN ref ref1
ON ref1.ID = i.ID1
LEFT OUTER JOIN ref ref2
ON ref2.ID = i.ID2
LEFT OUTER JOIN ref ref3
ON ref3.ID = i.ID3

You connect the reference table three times, using a left outer join, which lets the reference table not match the IND table, resulting in nulls where you had missing IDs. Then you select the v value from each of these joined tables, being sure to alias it, so you can tell them apart.

Upvotes: 1

Related Questions