CODEWITHSUNDEEP
pythonsqlsqlite
Simbi
Simbi

Reputation: 1042

Nesting Select queries on select part of query

I want to nest two SELECT queries where the result of the "inner" query should be used to perform a multiplication for every tuple in the "outer" query.

The "inner" query should also access a field of the outer table (tA.time in this example).

Example:

  SELECT r1 * (SELECT r2 FROM tB WHERE time < tA.time)
    FROM tA
   WHERE xyz
GROUP BY xyz

First question: Is this possible in general?

2nd question: Is this possible using SQLite?

3rd question: Did I sketch the query the right way? I tried to run it that way but I couldn't make it work by now.

Upvotes: 1

Views: 147

Answers (1)

cdhowie
cdhowie

Reputation: 169008

As long as the subquery returns exactly one row, this should work on most SQL-compliant databases. I have done similar things in SQLite and I suspect that it will work.

(If the subquery returns more than one row, there will be an error. If it returns no rows, the result of the multiplication operation will be NULL.)

Upvotes: 1

Related Questions