Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

HOWTO: How To Create an SQL Outer Join Using the View Designer


View products that this article applies to.

This article was previously published under Q151797

↑ Back to the top


Summary

This article explains how to create an outer join on a SQL Server table using the Remote View Designer. An outer join is necessary if the View needs to display not only matching rows between tables (rows with values in the specified columns that satisfy the join condition), but also non- matching rows. An outer join can only be performed if the backend DBMS supports it. This article uses the pubs database in Microsoft SQL Server.

↑ Back to the top


More information

This query uses the Authors table and the Publishers table and shows not only authors who have publishers in the same city, but also authors without publishers in the same city.

  1. Create a datasource to the pubs database on SQL Server.
  2. Create a new remote View, and select your new datasource created in step 1.
  3. Select the Authors table.
  4. Click again, and select the Publishers table.
  5. The join condition dialog appears showing:
    Publishers.city - Authors.city
  6. Change the join condition to Right Outer Join.
  7. Select the following fields from the Authors table:
    authors.au_fname
    authors.au_lname
  8. Select the following field from the Publishers table:
    Publisher.pub_name
  9. Run the query.

↑ Back to the top


References

"SQL Server Database Developers Companion", and "Visual FoxPro Developer's Guide."

↑ Back to the top


Keywords: KB151797, kbhowto

↑ Back to the top

Article Info
Article ID : 151797
Revision : 4
Created on : 10/15/2003
Published on : 10/15/2003
Exists online : False
Views : 495