Rob774 Rob774 - 3 years ago 133
SQL Question

Single-row subquery returns more than one row in Oracle

I am trying to build a simple query for working out times when staff clock in and clock out. The DB records the person's name, if their clocking in or out and the time. It records more then that but I don't need that info.

So an example DB would be:

Id | User | In_out | Date_Time
2 | Bob | In | 13/Oct/16 9:30:35AM
3 | Ken | In | 13/Oct/16 9:34:27AM
4 | Jon | In | 13/Oct/16 9:34:46AM
5 | Billy | In | 13/Oct/16 9:52:06AM
6 | Bob | Out | 13/Oct/16 4:30:05PM
7 | Jon | Out | 13/Oct/16 4:32:55PM

The result I want to bring back in a SQL is:

User | Time_In | Time_Out
Bob | 9:30:35AM | 4:30:05PM
Jon | 9:34:46AM | 4:32:55PM

The SQL I am trying is not right I know that. But what do I need to change to get the result I want?

(SELECT TO_CHAR(Clock.Checkin_Checkout.DATETIME, 'hh:mi:ss AM')
FROM Clock.Checkin_Checkout
WHERE Clock.Checkin_Checkout.In_Out = 'In') AS "Time In",
To_Char(Clock.Checkin_Checkout.Create_Datetime, 'hh:mi:ss AM') AS "Time Out"
Clock.Checkin_Checkout.In_Out = 'Out'
AND Clock.Checkin_Checkout.Datetime >= '13/Oct/2016'

Answer Source

I don't think you need a sub-query. Something like the following may work...

select c1.User, c1.Date_Time as "Time_In", c2.Date_Time as "Time Out"
from Clock.Checkin_Checkout c1
inner join Clock.Checkin_Checkout c2 on c2.User = c1.User
where c1.In_Out = 'In'
and c2.In_Out = 'Out'

Please excuse possible syntax errors. I haven't used Oracle for ages, and I don't have any way to test this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download