CODEWITHSUNDEEP
c#sqlsqlite
Doro
Doro

Reputation: 671

Create a view processing the original table using sqlite db

I am working on a C# Winforms application and trying to create a view in sqlite using the following table structure:

enter image description here

and this is how the view should look like: enter image description here

This is the sql command but does not produce the view as wished:

SELECT tbl1.Time AS C1,
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 2) AS C2, 
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 3) AS C3,
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 4) AS C4, 
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 5) AS C5,
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 6) AS C6, 
(SELECT MIN(Value) FROM tbl1 AS T WHERE Time = T.Time AND T.VarId = 7) AS C7
FROM tbl1
WHERE tbl1.VarId IN (1, 2, 3, 4, 5, 6, 7)
GROUP BY tbl1.Time
ORDER BY tbl1.Time

I really appreciate any help.

Upvotes: 0

Views: 78

Answers (1)

CL.
CL.

Reputation: 180070

In a subquery, a column name without a table name (like Time) refers to the innermost table, in this case, T. This means that Time = T.Time is interpreted as T.Time = T.Time.

For a correlated subquery, you must compare a column of the inner table (T.Time) and a column of the outer table (tbl1.Time):

SELECT tbl1.Time AS Time,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 1) AS C1,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 2) AS C2,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 3) AS C3,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 4) AS C4,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 5) AS C5,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 6) AS C6,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 7) AS C7,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 8) AS C8,
(SELECT MIN(Value) FROM tbl1 AS T WHERE T.Time = tbl1.Time AND T.VarId = 9) AS C9,
tbl1.Method AS Method    -- ???
FROM tbl1
GROUP BY tbl1.Time
ORDER BY tbl1.Time

Upvotes: 1

Related Questions