Thread: 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
Attachment
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
Attachment
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