SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best approach for "walking" a 1:m relationship?

    Hi, folks. Suppose I need to create invoices for a set of customers, each invoice consisting of a block of customer information followed by a variable number of detail lines. Standard stuff, but I'm used to reading records instead of executing queries, so I'm a little confused here.

    Do I use one query to gather the desired set of customers, and then loop through the set of customers, issuing a query for each customer's detail lines? Or do I use one query that gathers the desired set of customers AND their detail lines all at once?

    In the first case, I see that many more queries are involved, while in the second case, the customer information is needlessly repeated in each row. However, the amount of data involved is not large enough to make a performance difference either way, so I'm asking more if there's a standard way.

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    normally, you could get all the info (invoices etc etc) for a customer(s) using a single query.

    the info would normally be stored in multiple tables and you join the tables using common columns to get the required output from each table.

    we would really need to see the table structure of your tables in order to provide you more detail unless you want someone to post a generic plain vanilla basic example.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    A single query to gather all the necessary data is preferred over multiple queries when not utilizing some type of ORM automation, caching layer. When using an ORM, with cache support it would probably be more efficient, in the long run to issue single queries for each separate entity. To gain the advantage of an ORM, it is many times necessary to break up what can be done in a single queries into multiple requests. However, that doesn't seem to be the case here, so stick to a single query.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Kalon and oddz, a single query it is.


    And yes, the data is already stored in two tables. Briefly,

    Table: Customers
    RecID int
    Name string
    Address string
    City string
    yada-yada

    Table: Detail
    CustID int
    RecID int
    Date date
    ProductID int
    Qty int
    Price
    yada-yada

    I just wasn't clear on the preferred approach to get the data for reporting purposes. Although using a single query gives you a table with lots of redundant data, I guess that's better than hammering the server with queries.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Bobby Hall View Post
    Although using a single query gives you a table with lots of redundant data, I guess that's better than hammering the server with queries.
    here's an alternate starategy -- Minimize Bandwith in One-to-Many Joins

    you might not think this technique would apply in this instance since the columns between the customers and the order details are going to be quite disiimilar... still, there's no problem doing it, really, if you can live with multiple NULL columns in the two different row types from the UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there doesn't have to be redundant data returned by the query.

    you can get just the columns you want for a particular customer

    example

    Code:
     
    select Name, Address, CustID, Date, ProductID
    from Customers c, Detail d
    where c.RecID = d.RecID
    and Name = 'fred flinstone';

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    you can get just the columns you want for a particular customer
    two things

    first, in your example, the customer data ~is~ repeated on each row, once per order detail

    second, post #1 clearly says "a set of customers"

    please try to keep up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ooops

    multi-tasking has never been my strong point

    working on too many things atm.

    Mrs K. has the going atm

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 -- Thanks for the UNION explanation. I may have to use that since I'm not getting a single query to work.

    The sticking point are the aggregate functions. The balance for each invoice is not stored but rather calculated from the detail records when needed. For a single customer, the SUM() function works just fine, but if the query includes the invoice detail of several customers (as it would during a batch print of invoices), the SUM() function gives me the sum of all transactions. If I add a GROUP BY clause, the SUM() then correctly gives me the balance for each customer, but it also returns only a one row summary per customer.

    Suppose...
    Code:
    Table: Transactions
    CustomerID: str
    TransID: int
    TransDesc: str
    TransAmt: int
    
    Data:
    Cust T Desc  Amt
    SMIT 1 Shoes 100
    SMIT 2 Purse  75
    FORD 3 Shoes 125
    SMIT 4 Pants  50
    FORD 5 Dress  60
    JONE 6 Scarf  10
    
    I want to issue a query that returns this:
    Cust T Desc  Amt Bal
    FORD 3 Shoes 125 185
    FORD 5 Dress  60 185
    JONE 6 Scarf  10  10
    SMIT 1 Shoes 100 225
    SMIT 2 Purse  75 225
    SMIT 4 Pants  50 225
    
    But
    SELECT CustomerID, TransID, TransDesc, TransAmt, SUM(TransAmt)
    FROM Transactions
    GROUP BY CustomerID
    returns just one row per CustomerID, like this.
    
    Cust T Desc  Amt Bal
    FORD 5 Dress  60 185
    JONE 6 Scarf  10  10
    SMIT 4 Pants  50 225
    So I'm hacking away. Meanwhile, any clues are very appreciated!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    are we still talking about the same problem as post #1?
    Code:
    SELECT t.CustomerID
         , t.TransID
         , t.TransDesc
         , t.TransAmt
         , s.Balance
      FROM Transactions AS t
    INNER
      JOIN ( SELECT CustomerID
                  , SUM(TransAmt) AS Balance
               FROM Transactions
             GROUP
                 BY CustomerID ) AS s
        ON s.CustomerID = t.CustomerID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha, you make it look so easy!

    I always knew that SQL wasn't a trivial thing to learn, but I didn't realize I'd hit the brick wall this soon. Rather than imposing on you to retype your book, I guess I'll just buy it.

    I was away the last couple of days for Thanksgiving holiday in the US. Thanks for your patient answers. I'm grateful for them as well.


Bookmarks

Posting Permissions

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