Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2012
    Posts
    25

    Lightbulb Unanswered: Newbie Need help

    I have three tables


    Customer

    CustomerID CustomerName Birthdate
    1 John Doe 1/1/1970 08:31 AM
    2 Jane Doe 1/1/1971 01:18 PM
    3 Jon Public 1/1/1972 11:58 PM
    4 Jane Public 1/1/1973 07:00 AM
    5 John Smith 1/1/1974 08:31 AM
    Order

    OrderID PO Number OrderDate
    1000 ABC123 1/1/2012 01:00 PM
    2000 112233 2/1/2012 02:00 AM
    3000 XYZ987 3/1/2012 03:00 PM
    4000 50000 4/1/2012 04:00 AM
    5000 Verbal 5/1/2012 05:00 AM
    CustomerOrders

    CustomerID OrderID IsShipped
    1 1000 False
    1 3000 True
    3 4000 False
    2 2000 True
    5 5000 True

    And the question is to Provide the SQL statement that will return all the CustomerNames who have never placed an order..!!!

  2. #2
    Join Date
    Oct 2012
    Posts
    25

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here is a fully NZDF solution for your problem. Note that it may not be an optimal solution, depending on other information that you have not provided.
    Code:
    CREATE TABLE Customer (
       CustomerID   TINYINT
    ,  CustomerName NVARCHAR(20)
    ,  Birthdate    SMALLDATETIME
       )
    
    INSERT INTO Customer  VALUES
    -- CustomerID CustomerName Birthdate
       (1, 'John Doe',    '1/1/1970 08:31 AM')
    ,  (2, 'Jane Doe',    '1/1/1971 01:18 PM')
    ,  (3, 'Jon Public',  '1/1/1972 11:58 PM')
    ,  (4, 'Jane Public', '1/1/1973 07:00 AM')
    ,  (5, 'John Smith',  '1/1/1974 08:31 AM')
    
    CREATE TABLE [Order] (
       OrderID      SMALLINT
    ,  [PO Number]  NVARCHAR(10)
    ,  OrderDate    SMALLDATETIME
       )
    
    INSERT INTO [Order] VALUES
    -- OrderID PO Number OrderDate
       (1000, 'ABC123', '1/1/2012 01:00 PM')
    ,  (2000, '112233', '2/1/2012 02:00 AM')
    ,  (3000, 'XYZ987', '3/1/2012 03:00 PM')
    ,  (4000, '50000',  '4/1/2012 04:00 AM')
    ,  (5000, 'Verbal', '5/1/2012 05:00 AM')
    
    CREATE TABLE CustomerOrders (
       CustomerID	SMALLINT
    ,  OrderId      NVARCHAR(10)
    ,  IsShipped	VARCHAR(5)
    )
    
    INSERT INTO CustomerOrders VALUES
    -- CustomerID OrderID IsShipped
       (1, 1000, 'False')
    ,  (1, 3000, 'True')
    ,  (3, 4000, 'False')
    ,  (2, 2000, 'True')
    ,  (5, 5000, 'True')
    
    SELECT Customer.*
       FROM Customer
       FULL OUTER JOIN CustomerOrders
          ON (CustomerOrders.CustomerID = Customer.CustomerID)
       FULL OUTER JOIN [Order]
          ON ([Order].OrderID = CustomerOrders.OrderId)
       WHERE  [Order].OrderID IS NULL
          
    DROP TABLE CustomerOrders
    DROP TABLE Customer
    DROP TABLE [Order]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2012
    Posts
    25
    for the same data Provide the SQL statement that will return the OrderID, CustomerName, and the age of the Customer in years at the time they placed an Order.

    I have tried it using

    select Order.OrderID, customer.customername,
    Customer.BirthDate - Order.OrderDate from Order
    JOIN CustomerOrders ON
    Order.orderID=CustomerOrders.OrderID
    JOIN Customer ON CustomerOrders.CustomerID=Customer.CustomerID

    error is Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'Order'.

    Help me pls??????

  5. #5
    Join Date
    Oct 2012
    Posts
    25
    I modified it as below
    select Order.orderID, Customer.customername, (Customer.BirthDate - Order.OrderDate) from CustomerOrders JOIN Order ON CustomerOrders.OrderID = Order.orderID
    JOIN Customer ON CustomerOrders.CustomerID=Customer.CustomerID

    and getting the same error?????

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    "Order" is a reserved word and should NOT be used as a table name. Change this table name to something that is not a reserved word, and see if the error happens again.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    When you subtract dates from one another, you don't get the number of years between the dates. What did the professor say about getting the difference between a couple of dates?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1) Take the code you have, and check to see if it works as you expect.
    2) If it fails, experiment or post all of the code and a description of what went wrong
    3) When it succeeds, analyze it to understand what you've got that works
    4) Add one calculation
    5) Repeat until complete

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Oct 2012
    Posts
    25

    Thumbs up

    Yay... I used a datediff function to solve the problem.. here is the code


    select orders.orderID, customer.customername, DATEDIFF ( YY,customer.BirthDate,Orders.OrderDate) AS AGE from CustomerOrders JOIN Orders ON CustomerOrders.OrderID = Orders.orderID
    JOIN Customer ON CustomerOrders.CustomerID=Customer.CustomerID

  10. #10
    Join Date
    Oct 2012
    Posts
    25
    For the same info provided above... It would be really great if some one can help me find out the solution or method for below Problem..

    An sql statement that would inverse the IsShipped field, but only for customers that where born at the exact same time of day??????

  11. #11
    Join Date
    Oct 2012
    Posts
    25
    ?????????????????????????????????????????????????? ?

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you have a new problem, start a new thread. Otherwise it will not come up when people with similar problems perform searches in the future.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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