Sqlite select query using result value from another select query from another table

Question

I have checked the similar answer but didn't find what i am looking for.

I have a table from where I get a value like below:

SELECT value1 from table1;
b3

I need to use this result for my next query:

SELECT b1,b2,b3,b4,b5,(SELECT value from table1) from table2;
15.26|13.14|11.08|9.05|7.05|b3
15.31|13.2|11.14|9.11|7.12|b3
15.37|13.26|11.2|9.18|7.18|b3
15.43|13.32|11.26|9.24|7.25|b3

the problem is that the SELECT statement is returning b3 instead of b3 values as per the following result (last column is return of b2 from the select)

SELECT b1,b2,b3,b4,b5,(SELECT value from table1) from table2;
15.26|13.14|11.08|9.05|7.05|11.08
15.31|13.2|11.14|9.11|7.12|11.14
15.37|13.26|11.2|9.18|7.18|11.2
15.43|13.32|11.26|9.24|7.25|11.26

any idea how to solve this? many thanks

Show your data in both tables and your expected results. Its unclear atm — Thomas G
– Thomas G, Commented Jan 5, 2020 at 15:02
The result b3 that you see is the value of the column value1, but b1,b2,b3 are the names of the columns with values 100,200,13.23. These are 2 different things. — forpas
– forpas, Commented Jan 5, 2020 at 15:04
sorry, my bad, I have badly formatted my question, i have added some clarifications. pls let me know if this is clear — joe
– joe, Commented Jan 5, 2020 at 15:17

forpas · Accepted Answer · 2020-01-05 15:22:03Z

1

I suspect that you want a CASE expression:

SELECT b1,b2,b3,b4,b5,
  CASE (SELECT value from table1) 
    WHEN 'b1' THEN b1
    WHEN 'b2' THEN b2
    WHEN 'b3' THEN b3
    WHEN 'b4' THEN b4
    WHEN 'b5' THEN b5
  END result  
FROM table2

but this will work only if:

SELECT value from table1

returns only 1 row.
See the demo.
Results:

| b1    | b2    | b3    | b4   | b5   | result |
| ----- | ----- | ----- | ---- | ---- | ------ |
| 15.26 | 13.14 | 11.08 | 9.05 | 7.05 | 11.08  |
| 15.31 | 13.2  | 11.14 | 9.11 | 7.12 | 11.14  |
| 15.37 | 13.26 | 11.2  | 9.18 | 7.18 | 11.2   |
| 15.43 | 13.32 | 11.26 | 9.24 | 7.25 | 11.26  |

edited Jan 5, 2020 at 15:22

answered Jan 5, 2020 at 15:09

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joe Over a year ago

this is working as expected, many thanks for your help. is it possible to not specify all the columns? b1-b5. how can it be done in an efficient way if we have 100 columns?

forpas Over a year ago

There is no direct link between a string like 'b3' with a column's name like b3, so any solution would have to enumerate all the columns. This is because of the design of Table2.

Collectives™ on Stack Overflow

Sqlite select query using result value from another select query from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related