Thread: Scalar subqueries

__sorry for posting it second time__

Not sure what am I missing. I really appreciate if anyone could point it out
to me.

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.

playfield=# select * from logs;log_id |  log_creation_date  | account_id | user_id | service | action |
mac     
--------+---------------------+------------+---------+---------+--------+---
----------     1 | 2004-04-29 10:48:36 | Robert     |       3 |       5 |      2 |
00-00-00-00     3 | 2004-04-29 10:53:44 | Robert     |       3 |       5 |      3 |
00-00-00-00     5 | 2004-04-29 11:11:35 | Robert     |       3 |       5 |      1 |
00-00-00-00  1003 | 2004-05-03 15:18:53 | Robert     |       3 |       5 |      5 |
00-00-00-00  1004 | 2004-05-03 15:19:50 | Robert     |       8 |       5 |      1 |
00-00-00-00
(5 rows)

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

Select I wrote :

select log_id as signin_id,     (select foo.log_id      from         (select foo1.log_id          from logs as foo1
    where foo1.action in (3,4,5,6,7)            and l.log_id > foo1.log_id          order by foo1.account_id,
foo1.user_id,foo1.mac,
 
foo1.log_creation_date) as foo limit 1) as signout_id
from logs as l         
where action in (1,2);

Gives...
signin_id | signout_id 
-----------+------------        1 |                   5 |          3     1004 |          3
(3 rows)

Expected output :
signin_id | signout_id 
-----------+------------        1 |          3        5 |       1003     1004 |          
(3 rows)

Thanks,
Stalin

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