novice wrote:
2008/11/20 brian <[email protected]>:
call_id | datetime | user_id | mobile_no | charge
---------+---------------------+---------+-------------+--------
1 | 2007-10-12 10:00:00 | | 09455225998 | 4.5
2 | 2007-10-16 13:27:00 | | 09455225998 | 5.2
2008/11/20 brian <[email protected]>:
novice wrote:
SELECT call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no,
call.charge
FROM call
LEFT JOIN mobile_custodian
ON call.mobile_no = mobile_custodian.mobile_no
AND call.datetime
BETWEEN
mobile_custodian.issue_date
AND
mobile_custodian.return_date;
this gave me nothing on the user_id field :(2008/11/20 Adam Rich <[email protected]>:
How about:select call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no
call.charge
from call, mobile_custodian
where call.mobile_no = mobile_custodian.mobile_no
and call.datetime between mobile_custodian.issue_date
and mobile_custodian.return_date
sorry I get nothing :(call.datetime,
mobile_custodian.user_id,
call.mobile_no
call.charge
from call, mobile_custodian
where call.mobile_no = mobile_custodian.mobile_no
and call.datetime between mobile_custodian.issue_date
and mobile_custodian.return_date
SELECT call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no,
call.charge
FROM call
LEFT JOIN mobile_custodian
ON call.mobile_no = mobile_custodian.mobile_no
AND call.datetime
BETWEEN
mobile_custodian.issue_date
AND
mobile_custodian.return_date;
call_id | datetime | user_id | mobile_no | charge
---------+---------------------+---------+-------------+--------
1 | 2007-10-12 10:00:00 | | 09455225998 | 4.5
2 | 2007-10-16 13:27:00 | | 09455225998 | 5.2
This is the expected result given the sample data you provided. If your
sample data for call table should be 2008 instead of 2007; then output
would be:
call_id | datetime | user_id | mobile_no | charge
---------+---------------------+---------+-------------+--------
2 | 2008-10-14 13:27:00 | | 09455225998 | 5.2
1 | 2008-10-12 10:00:00 | Ben | 09455225998 | 4.5
user_id is still empty on call_id=2 because neither custodian had the
mobile on Oct.14!