From: | Matt Foster <Matthew(dot)Foster(at)noaa(dot)gov> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Need help with OUTER JOIN |
Date: | 2011-11-18 18:24:30 |
Message-ID: | 4EC6A2DE.4080000@noaa.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 11/18/2011 11:48 AM, Thom Brown wrote:
> On 18 November 2011 17:44, Matt Foster<Matthew(dot)Foster(at)noaa(dot)gov> wrote:
>> I'm having trouble getting the desired results from an OUTER JOIN, and I'm
>> hoping I can get some help here.
>>
>> My first table, office_list, has a column, office, that is a static list of
>> all field office identifiers in our area. The second table,
>> verification_data, contains data that is submitted daily by the field
>> offices. The verification_data table contains columns for the office ID
>> (also office), a timestamp (start_time), and other various data. I'm
>> needing to perform a query that will always give me a complete list of
>> offices (i.e. everything from the first table), plus a field from the second
>> table, and is also conditional on the timestamp and a few other tests.
>>
>> Here is what I have been trying, unsuccessfully...
>>
>> SELECT office_list.office, verification_data.period
>> FROM office_list
>> LEFT OUTER JOIN verification_data USING (office)
>> WHERE start_time> 'yesterday'
>> AND start_time< 'today'
>> AND period=1
>> AND name='foo'
>> AND element='bar';
>>
>> The result is that I only get rows where there are matches between the two
>> tables.
>>
>> Where have I gone awry?
> Well are there any records in verification_data which don't have an
> "office" column value which is listed in the office_list table? If
> not, all records will match. I'm not sure what you're expecting to
> get back.
>
Thom,
I think I'm seeing the error of my ways now.
I'm needing to look at a particular date or range of dates in the column
start_time, and see what offices did not submit their data. If an
office has not submitted their data, there is no row at all from the
verification_data table to match in any way to the office_list table.
I need to figure out a different way to get the result I want. What I
want is: a list of offices from office_list that do NOT appear in
verification_data according to the where conditions in the original query.
Matt
--
Do not go where the path may lead; go instead where there is no path and leave a trail.
-- Ralph Waldo Emerson
Attachment | Content-Type | Size |
---|---|---|
matthew_foster.vcf | text/x-vcard | 204 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Yves F. Barbier | 2011-11-18 18:26:06 | Re: Real novice question: Roles |
Previous Message | Richard Broersma | 2011-11-18 18:15:06 | Re: Need help with OUTER JOIN |