#1
  1. No Profile Picture
    Loves Diesel Generators
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Location
    Lincs - UK
    Posts
    661
    Rep Power
    83

    Subtable Data GROUPBY


    Hi,

    I am working on a product sales system and there are two tables involved. The first holds a selection of sold products, the second a selection of extras that relate to these sold products.

    So for example, the products table:

    product_id, int
    product_description, varchar
    product_sell_price, decimal(10,2)

    then the extras table:

    product_id, int (value from products table)
    extra_description, varchar
    extra_sell_price, decimal(10,2)

    Currently I do a select that groups by the price and decription to identify unique products on the invoice. This however is now not good enough, as the extras can change the value and makeup of the product.

    I can create a function, to make sure the sets on the invoice by using SUM(extra_sell_price) + product_sell_price, which will make sure the overall values are the same, but what about making sure the descriptions are the same?

    Can i somehow "sum()" using a group function the text descriptions to create one long string, which I could then MD5 hash to make sure they are the same?

    What other ways could you suggest?
    If I helped you by some stroke of luck, please add to my reputation by clicking on the icon at the top of my post!
    Perkins Diesel Generators Volvo Diesel Generators xxl.me.uk (my Site)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    identify unique products on the invoice
    What invoice?
    groups by the price and decription to identify unique products
    Why would price make something unique? Surely you could have two (or more) products with the same price? Does not the product_id identify a unique item?

    Would something like (untested!) do what you need?:

    Code:
    SELECT p.product_id
         , p.product_sell_price + SUM(x.extra_sell_price) as Total
      FROM products p
        LEFT JOIN extras x
          ON p.product_id = x.product_id
      GROUP BY p.product_id
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Loves Diesel Generators
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2004
    Location
    Lincs - UK
    Posts
    661
    Rep Power
    83
    I am afraid it doesn't.

    Imagine that the products are cars. The product Id identifies the base model, say 2l ford mondeo. Then the extras then include sports seats, a sunroof and AC.

    There might be 10 cars on the order, all the same product id, 5 of which have the same extras, the others all have different extras.

    On the invoice, I will group by product id, but I need also to "group by" the whole extras list.

    ....... Ahhhhhhhhh! Maybe I have just answered by own question. I will have a go at that! A subquery doing a select on a result set... Let me see.....
    If I helped you by some stroke of luck, please add to my reputation by clicking on the icon at the top of my post!
    Perkins Diesel Generators Volvo Diesel Generators xxl.me.uk (my Site)

IMN logo majestic logo threadwatch logo seochat tools logo