[wellandpower]

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?

[SimonJM]

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?:

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

[wellandpower]

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.....