[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Join Syntax help

Posted on 2005-05-06
3
Medium Priority
?
240 Views
Last Modified: 2010-03-19
I'm trying to modify some queries to use ANSI SQL.  I'm practicing in Northwind to start.  I have query A which returns all customers showing USA as the country where appropriate.  How do I change query B to match the output of query A?

--QUERY A
SELECT
      A.CustomerID,
      A.CompanyName,
      B.OrderID,
      B.ShipCountry
FROM
      Customers A,
      Orders B
WHERE
      A.CustomerID *= B.CustomerID
      AND B.ShipCountry = 'USA'

--QUERY B
SELECT
      A.CustomerID,
      A.CompanyName,
      B.OrderID,
      B.ShipCountry
FROM
      Customers A LEFT OUTER JOIN Orders B
      ON A.CustomerID = B.CustomerID
WHERE
      B.ShipCountry = 'USA'
0
Comment
Question by:TommyTupa
  • 2
3 Comments
 
LVL 13

Author Comment

by:TommyTupa
ID: 13949074
Sorry let me change QUERY A to this:
SELECT
      A.CustomerID,
      A.CompanyName,
      B.OrderID,
      B.ShipCountry
FROM
      Customers A,
      Orders B
WHERE
      A.CustomerID *= B.CustomerID
      AND B.ShipCountry = 'USA'
      AND B.OrderDate > '01/01/1998'
Order By
      A.CustomerID
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13949148
SELECT      A.CustomerID,
      A.CompanyName,
      B.OrderID,
      B.ShipCountry
FROM      Customers A
      LEFT JOIN Orders B ON A.CustomerID = B.CustomerID And B.ShipCountry = 'USA'
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13949151
SELECT     A.CustomerID,
     A.CompanyName,
     B.OrderID,
     B.ShipCountry
FROM     Customers A
     LEFT JOIN Orders B ON A.CustomerID = B.CustomerID And B.ShipCountry = 'USA' AND B.OrderDate > '1998-01-01'
ORDER BY A.CustomerID
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question