Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Selecting the first row of a joining table only

    I have a query with three tables

    Table Customer
    Table Purchases
    Table Purchases details

    Customer 1 - m Purchases 8 - 1 Purchases details

    What I am looking for is to make a query that selects all customers, but only the first of their purchases.

    Something in the lines of

    Code:
    SELEC customer.*, purchases.*
    FROM customer INNER JOIN purchases on customer.ID = purchases.custid
    Yet only return 1 line from the purchases table...

    Must be some special JOIN operator?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's the PurchaseDetails table for, multiple products per purchase?

    "first of their purchases" implies a datetime field somewhere -- which table would it be in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1

    first of their purchases

    hello
    see the attached db.
    I've tried to imagine some but the queries might just be what you may be looking for.

    I've essentially used the first()

    also checkout this link for what microsoft says abt natural sort order to find which record was entered first and which the last

    hope i was some help
    tc
    Attached Files Attached Files
    Abhi
    A2K3 WinXP Prof SelfLearner

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    an incrementing id will work too, but will never allow you to retroactively add rows anywhere except at the end

    a datetime is much safer
    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
  •