CODEWITHSUNDEEP
mysql
Do0m
Do0m

Reputation: 21

How to get a field from a table twice(both on different conditions)?

I have one table called Reader with id,date,time as its fields . The time field contains both the in-time and out-time of the employees hence i would like to get retrieve the value present in time twice based on 2 conditions for a particular id and a range of dates:

1.In-time:Time between '9:00:00' and '12:00:00'
2.Out-time:Time between '15:00:00' and '19:00:00'

The Command should some what look like this

select id,tim,tim,dat from reader 
where tim between '09:00:00' and '14:00:00' 
and   tim between '15:00:00' and '17:00:00' 
and dat between '2011-01-17' and '2011-01-19' 
and id =10704 ;

Upvotes: 1

Views: 382

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

select id,tim,dat from reader 
where ((tim between '09:00:00' and '14:00:00')
    or (tim between '15:00:00' and '17:00:00'))
and dat between '2011-01-17' and '2011-01-19' 
and id =10704 ;

You need an OR between the times, not AND, since it can never be possible for a time to be within TWO non-intersecting ranges. Make sure to bracket that part of the condition, or your AND/ORs won't work properly.

The above gives you the data in separate rows. To get them flat on the same record, you could use, assuming only one time in each range per day

select rin.id,rin.tim,rout.tim as timeout,rin.dat
from reader rin
left join reader rout on rout.id=rin.id
    and rout.tim between '15:00:00' and '17:00:00'
    and rin.dat = rout.dat
where rin.tim between '09:00:00' and '14:00:00'
and rin.dat between '2011-01-17' and '2011-01-19' 
and rin.id =10704
union all
select rout.id,rin.tim,rout.tim as timeout,rout.dat
from reader rout
left join reader rin on rout.id=rin.id
    and rin.tim between '09:00:00' and '14:00:00'
    and rin.dat = rout.dat
where rout.tim between '15:00:00' and '17:00:00'
and rout.dat between '2011-01-17' and '2011-01-19' 
and rout.id =10704
and rout.id is null

This special UNION ALL form emulates a FULL JOIN which is missing in MySQL. You will get to see data even when an employee clocks in but not out or out but not in on any day.

Upvotes: 1

royas
royas

Reputation: 4936

I think that adding AND r1.dat=r2.dat to Thilo's code might be useful if you want to group ins and outs from one day in one row.

Upvotes: 1

Thilo
Thilo

Reputation: 9157

you should be able to do it somehow like this: (I assume you have another field giving information wether it is in or out. Otherwise, you base your query on a very weak assumption. However:

SELECT r1.id, r1.tim, r2.tim, r1.dat 
FROM reader AS r1, reader AS r2 
WHERE r1.id = 10704 AND r2.id = 10704
    AND r1.tim BETWEEN '09:00:00' AND '14:00:00' 
    AND r2.tim BETWEEN '15:00:00' AND '17:00:00' 
    AND r1.dat BETWEEN '2011-01-17' AND '2011-01-19';

Note that I put the condition for the id at the very top, assuming it is the most restrictive one and thus maybe gaining a bit of performance (Which might the query optimizer do by itself in good cases)

Cheers Thilo

Upvotes: 0

Related Questions