i have this query which i use to match two tables, now, i match the tables only by the time, so, which means, the time field in retailer table is 5 seconds earlier then the time field in imovo table. What i already need to achieve is:
When i insert a retailer row, i add info like retailer time, retailer epos_id When i insert imovo row, i add info like imovo time, imovo rpos_id I also have a table rbpos_epos which has matching rbpos_id and epos_id i.e
table retailer:
time | epos_id
12:23:34 ea12
table imovo
time | rbpos_id
12:23:29 ra12
table rbpos_epos
id | epos_id | rbpos_id
1 ea12 ra12
When i match retailer with imovo, i want also to check whether their respective rbpos_id and epos_id match in the rbpos_epos table.. i mean, if they are a pair, if they have the same id..
here's the query i currently use
SELECT retailer.date, retailer.time, retailer.location, retailer.user_id,imovo.mobile_number ".
"FROM retailer LEFT JOIN imovo ".
" ON addtime(retailer.time, '0:0:50')>imovo.time
AND retailer.time <imovo.time AND retailer.date=imovo.date
"
Try joining all three tables SQL Fiddle
select r.`time`, r.`epos_id`,
i.`time`, i.`rbpos_id`
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
and addtime(r.`time`, '0:0:50') > i.`time`
and r.`time` < i.`time`
and r.`date` = i.`date`;
Add columns in select as needed.