Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Cool Unanswered: Problem in connecting tables where foreign key is null

    Hi folks,

    I want to display records from following two tables which are joined by OrderID.
    Customer
    ID, Name, OrderID

    Order
    OrderID, OrderDate

    The SQL I am using to pull data is,
    SELECT c.ID, c.Name, o.OrderID, o.OrderDate from Customer AS c, Order AS o WHERE c.OrderID = o.OrderID

    But I need to display customers without Orders as well where the OrderID field in Customer table is NULL. How can I do this ?
    Thanks in advance.

    Asela

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you move to ANSI join syntax, you can then use a LEFT join to achive what you want.
    Code:
    SELECT c.ID
         , c.Name
         , o.OrderID
         , o.OrderDate
    FROM   Customer As c
     LEFT
      JOIN Order As o
        ON c.OrderID = o.OrderID
    Essentially this query says "Take all records from the customer table (the left one in the join) and where possible, find it a match in the orders table, based on the orderid field"


    As a side note, your table designs look a tad flawed... Can one customer only make one order?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    Hi George,

    Thanks a lot for immediate reply and the solution.
    Fixed the issue I was having then and there.
    Thanks again.

    Asela

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •