Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: help with tricky query in MSSQL

    Let's say that I have three tables:

    Buyer
    ------
    ID
    Name
    Adress

    Session
    -------
    ID
    Date
    Pageviews
    Buyer

    Orders
    -------
    ID
    DatePaid
    Session

    Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.

    Any help would be enourmously appriceated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select Buyers.ID, Buyers.Name, count(Orders.ID)
    from Buyers
    left outer join Session on Buyers.ID = Session.Buyer
    left outer join Order on Session.ID = Order.Session and Orders.DatePaid is not null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Posts
    3
    Thank you so much!

    You rock!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jeepers, mattias, did you notice how similar this solution is to the one i gave you?

    so, inquiring minds wanna know, which one did you use? inner or left outer joins?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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