From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: [SQL] [GENERAL] date range query help |
Date: | 2008-11-20 04:19:05 |
Message-ID: | 4924E539.90603@sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice pgsql-sql |
novice wrote:
> 2008/11/20 brian <brian(at)zijn-digital(dot)com>:
>> novice wrote:
>>> 2008/11/20 Adam Rich <adam(dot)r(at)sbcglobal(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | novice | 2008-11-20 04:32:53 | Re: [SQL] date range query help |
Previous Message | Rodrigo E. De León Plicet | 2008-11-20 04:17:18 | Re: [SQL] date range query help |
From | Date | Subject | |
---|---|---|---|
Next Message | novice | 2008-11-20 04:32:53 | Re: [SQL] date range query help |
Previous Message | Rodrigo E. De León Plicet | 2008-11-20 04:17:18 | Re: [SQL] date range query help |
From | Date | Subject | |
---|---|---|---|
Next Message | novice | 2008-11-20 04:32:53 | Re: [SQL] date range query help |
Previous Message | Rodrigo E. De León Plicet | 2008-11-20 04:17:18 | Re: [SQL] date range query help |