Re: Scalar subqueries

On Mon, Jun 14, 2004 at 12:38:44PM -0700, Subbiah, Stalin wrote:
> 
> I've a logs table that has both sign-in and sign-out records which are
> differentiated by action flag. Records with action flag = (1,2) => sign-in
> records and action flag = (3,4,5,6,7) => sign-out records.

<snip>

> All I'm trying to do is print signin id and corresponding sign-out id's in
> single row.

You're writing one more level of sub-select than you actually need.

SELECT inlog.log_id AS signin_id, (SELECT MIN(outlog.log_id)FROM logs outlogWHERE outlog.log_id > inlog.log_idAND
actionIN (3, 4, 5, 6, 7)
 
) AS signout_id
FROM logs inlog
WHERE inlog.action IN (1, 2);

Assuming you want to match signins and signouts by the same account to
the same service, or whatever, you can add in clauses like

AND outlog.account_id = inlog.account_id

or whatever else you like, to the inner select.


Richard