novice wrote:
> 2008/11/20 brian <brian@zijn-digital.com>:
>> novice wrote:
>>> 2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>>>> 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 :(
>>>
>> How about:
>>
>> 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 :(
>
> 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!