ASP - MS SQL Recordset to Join 4 Tables

Hi

I am having a real problem getting my head around joining some tables.

I am trying to pull data from 4 tables

Table1 = priceinfo  - (holds price info of our product, sometimes updated twice a day)
Table2= stockinfo - (holds the in/out of stock info of product, sometimes updated twice a day - if the product is in stock the column "instock" =1  if out of stock the column "instock" =0)
Table3= deliverycost ( holds the productid , site, and actual delivery cost)
Table4 =vouchercode (holds the productid, site, vouchercode, vouchervalue, voucherstart and voucher end dates - the vouchervalue is store like "10" which will give a 10% discount)

all tables have two identical columns
productid & site

Productid is a unique code and stored as CHAR (14)
site varies there can be upto 14 different site names for each productid

I have a repeat region table in which I am trying to display the following information

site - price - deliverycost - in/out of stock - Total(price+delivery)

I have so far managed to join just the deliverycost table and as you'll see my code is prob not the best :(

what baffles me more than anything is how I will calculate the vouchervalue  as a % of Total(price +delivery)

I really need some help as you'll probable notice by the attached code

Thanks in advance

Jason

Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Dim Recordset1
Dim Recordset1_numRows
 
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_luxurygifts2_STRING
Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='12345678' group by site) group by a.site,a.id,a.updatedatetime, a.productid, a.price, b.deliverycost  ORDER BY SUM(a.price + b.deliverycost) asc"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open
 
Recordset1_numRows = 0