CODEWITHSUNDEEP
sqlsqlite
Radamand
Radamand

Reputation: 185

SQLite join not working

I have an SQL which selects some rows, I want it to also select the row following each row I selected, I cannot figure out how to write such a query...

The simple select:

SELECT myIndex, scr, date from history where x.state = "Move to Test"

My latest attempt:

SELECT myIndex, scr, date
from history as x cross join history as y on y.myIndex + 1 = x.myIndex
where state = "Move to Test"

Which gives: "ambiguous column name: myIndex"

If I were to specify the column tables like this;

SELECT x.myIndex, x.scr, x.date
from history as x cross join history as y on y.myIndex + 1 = x.myIndex
where x.state = "Move to Test"

I get nothing at all selected from the 'y' table...

Here's an example of what my output should look like:

391 200024   2006-11-27 16:03:43     Move to Test     < I am selecting this row
392 200024   2006-11-27 16:21:02     Dev              < I want to also select the *next* row, regardless of what's in it
395 200024   2006-11-29 10:58:17     Move to Test     < selected
396 200024   2006-11-29 12:10:29     Peer Review      < next
402 200024   2006-11-30 07:27:51     Move to Test     < selected
403 200024   2006-11-30 08:26:13     TQA              < next
408 200024   2007-05-03 08:35:40     Move to Test     < selected
409 200024   2007-05-03 08:52:53     Dev              < next

Upvotes: 1

Views: 555

Answers (3)

Dan
Dan

Reputation: 13160

Just add or y.state = "Move to Test" to your last query, and change the + to a -.

Upvotes: 1

CL.
CL.

Reputation: 180020

In SQLite 3.8.3 or later:

WITH indexes AS (SELECT myIndex
                 FROM history
                 WHERE state = 'Move to Test')
SELECT myIndex,
       scr,
       date
FROM history
WHERE myIndex     IN indexes
   OR myIndex - 1 IN indexes

In earlier versions, you cannot use the CTE:

SELECT myIndex,
       scr,
       date
FROM history
WHERE myIndex     IN (SELECT myIndex FROM history WHERE state = 'Move to Test')
   OR myIndex - 1 IN (SELECT myIndex FROM history WHERE state = 'Move to Test')

Upvotes: 1

apomene
apomene

Reputation: 14389

If I am understanding correct you want myIndex from both tables, so why not use:

SELECT x.myIndex,y.myIndex x.scr, x.date
from history as x cross join history as y on y.myIndex + 1 = x.myIndex
where x.state = "Move to Test"

Upvotes: 0

Related Questions