0

I got a weird situation and I'm completely stuck at this ridiculous problem.

So basically I just want to use a subquery in a Select clause to get the column name for the query I want to set up. The query is the following SQLite snippet:

select (select Year from t2) from t1
where "Country Name" LIKE 'Afghanistan' AND
"Indicator Name" LIKE 'Population. total'
;

The (select Year from t2) part returns a real column of t1 (Table 1) which is a numeric type. e.g. 1960 or 1976.

So when I run the query, I expect the subquery to return the value for the column for the outer query - but it just doesn't works this way.

The whole query just returns a column with the name (select Year from t2) and the value 1960 (which I want to have as a select parameter representing a real column!)

If anyone of you got a solution to this problem I would be very grateful.

EDIT:

t1 structure (it is a very huge world population table with 100k+ rows):

Country Name | Indicator Name      | 1960    | 1961 ...
------------------------------------------------
Afghanistan  | Population.  total  | 8456000 | 9201000

t2 structure: (just a small table with 1 row which represents search parameters)

Country     | Year | SearchType
---------------------------------
Afghanistan | 1960 | Population. total

I just want to fetch the value '1960' from this table.

Later in the query (which is not implemented yet, I also need to fetch Country and SearchType)

clagger
  • 33
  • 5
  • Please share the table structure with some example data and explain which result your are trying to fetch from this table. – Christoph-Tobias Schenke Nov 26 '16 at 12:37
  • 1
    Am not a expert in sqlite but in general it will done by building the query dynamically and executing it. – Pரதீப் Nov 26 '16 at 13:10
  • 1
    *World Pop Table* is not designed well and hence your query challenges. Wide formats are usually for reporting/presentation purposes, while long format with *Year* and *Population* columns are the normalized standard design. Consider rebuilding table accordingly. – Parfait Nov 26 '16 at 14:05
  • I found out a nice (but dirty) workaround: I add the parameters on client side with string builder and add the whole sqlite script as a parameter in REST POST and push it to the related resource `string sqlParam = "select \"Country Name\", \"" + year + "\" from t1 where \"Country Name\" LIKE '" + country + "' AND \"Indicator Name\" LIKE"+searchType+" ;";` – clagger Nov 29 '16 at 09:02

0 Answers0