Обсуждение: Need help with OUTER JOIN

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?

Thanks!

Matt

--
Do not go where the path may lead; go instead where there is no path and leave a trail.
-- Ralph Waldo Emerson

On 18 November 2011 17:44, Matt Foster <Matthew.Foster@noaa.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

On Fri, Nov 18, 2011 at 9:44 AM, Matt Foster <Matthew.Foster@noaa.gov> wrote:

> 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';


1) Replace the USING() to ON office_list.office = verification_date.office

Find all of the columns in your WHERE clause that are in your
Verification_Data table and move these criteria to ON clause.

SELECT office_list.office, verification_data.period
FROM office_list
LEFT OUTER JOIN verification_data
ON Office_list.office = verification_data.office
AND start_time > 'yesterday'
AND start_time < 'today'
WHERE period=1
AND name='foo'
AND element='bar';



--
Regards,
Richard Broersma Jr.

On 11/18/2011 11:48 AM, Thom Brown wrote:
> On 18 November 2011 17:44, Matt Foster<Matthew.Foster@noaa.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

On 11/18/2011 12:15 PM, Richard Broersma wrote:
> On Fri, Nov 18, 2011 at 9:44 AM, Matt Foster<Matthew.Foster@noaa.gov>  wrote:
>
>> 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';
>
> 1) Replace the USING() to ON office_list.office = verification_date.office
>
> Find all of the columns in your WHERE clause that are in your
> Verification_Data table and move these criteria to ON clause.
>
> SELECT office_list.office, verification_data.period
> FROM office_list
> LEFT OUTER JOIN verification_data
> ON Office_list.office = verification_data.office
> AND start_time>  'yesterday'
> AND start_time<  'today'
> WHERE period=1
> AND name='foo'
> AND element='bar';
>
>
>

Hey...that works!  Thank you, Richard!

--
Do not go where the path may lead; go instead where there is no path and leave a trail.
-- Ralph Waldo Emerson