On Thu, 2011-09-15 at 10:42 -0700, John R Pierce wrote: > lets come up with a really simplistic example here. > > table: customers{id, name, address} > table: catalogitem(id,description,price} > table: customerorder{id,customer references customers(id),date} > table: orderlineitem{orderid references customerorder(id),catalogid > references catalogitem(id), qty} > > that data is normalized, there is no redundant data in any of those > tables, they are connected by the relations defined via the references > ('foreign keys'). I would not design my orders database exactly like you have. If I knew the system user wanted to know "how much customer named 'joe' has ordered in 2010," then I would first ask ? by value ? by quantity of different items ? by gross quantity of all items I might even make a table like this:- C1 c1ref c1customer (code) c1quantity (integers only) c1price (in cents) c1discount (2 decimal places held as integers) c1catalogue (code) c1date (yymmdd) c1order (number) c1comments (text) then do a query: select c1quantity, c1price, c1discount from c1 where c1customer = 'joebloggs' and c1date like '10%' while ... $value.= ($c1price*$c1quantity*((100-$c1discount)/100)); > now, if we want to pull up a summary of how much customer named 'joe' > has ordered in 2010, we'd do something like... > > select sum(ci.price*oi.qty) from customers c > join customerorders co on (co.customer=c.id) > join orderlineitem oi on (co.id=oi.catalogid) > join catalogitem cati on (cati.id=oi.catalogid) > where c.name = 'joe' and extract (year from co.date) = 2010; Never used SQL sum, so I would try select sum($c1price*$c1quantity*((100-$c1discount)/100)) from c1 where c1customer = 'joebloggs' and c1date like '10%' Not a 'join' insight :-) -- With best regards, Paul. England, EU. _______________________________________________ CentOS mailing list CentOS@xxxxxxxxxx http://lists.centos.org/mailman/listinfo/centos