Hi Guys, Im haveing trouble creating a table in ASP. Im willing to paypal the person who comes up with the best solution $5 . Im not even sure if its possible.. Anyways.... I want to create a table in in my ASP page with the following headings: Surname*Firstname*AddressNo*Address*AddressExt*Town*Route*Distributor*AverageSpend ********************************************* I have the following tables in my MySQL database: Distributor: distributorid, firstname, surname customer: customerid, firstname, surname, address, Town, routeid, distributorid, AddressNo, AddressExt customerOrders: Orderid, customerid, distributorid, details, total, date route: routeid, distrubtorid, routename, routedetails ************************************************ Ok, so a distributor has many customers who belong to a certain route, the customers make many orders. My problem is i need to pull out a query that will calculate the average customer orders, put in the distributor name and also the route name. And lastly I need to click on the headings, (especially AverageSpend) so I can order the data, by name, address etc Looking forward to your help! Any questions please ask.
select c.surname as [Surname], c.firstName as [First Name], c.addressNo as [Address No.], c.address as [Address], c.addressExt as [Address Ext.], c.town as [Town], r.routeName as [Route], (d.firstName + ' ' + d.surname) as [Distributor], (select avg(o.total) from customerOrders o where o.customerID = c.customerID and o.distributorID = d.distributorID) as [Average] from customer c inner join route r on c.routeID = r.routeID inner join distributor d on c.distributorID = d.distributorID There is the SQL. I'll provide the asp later. It's asp 3.0 or .Net? If you are using MySQL you may need to have the SQL read: ...Concat(d.firstName, ' ', d.surname)... instead of (d.firstName + ' ' + d.surname)