From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Matt Foster <Matthew(dot)Foster(at)noaa(dot)gov> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Need help with OUTER JOIN |
Date: | 2011-11-18 17:48:14 |
Message-ID: | CAA-aLv4LGX3Ok82HR0ssaCse5PBkkknhH7AxXO23Uo=wjWXb_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2011-11-18 18:15:06 | Re: Need help with OUTER JOIN |
Previous Message | Phil Dobbin | 2011-11-18 17:45:58 | Re: Real novice question: Roles |