Solved

ASP - MS SQL Recordset to Join 4 Tables

Posted on 2008-10-11
5
353 Views
Last Modified: 2010-07-27
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


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

Open in new window

0
Comment
Question by:sparky74
  • 4
5 Comments
 
LVL 11

Accepted Solution

by:
Goodangel Matope earned 500 total points
ID: 22696653
Here is how you can do it
Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice, (SUM(a.price + b.deliverycost) * c.vouchervalue / 100 ) as VoucherValue, Instock = d.instock FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid INNER JOIN vouchercode c ON c.productid = a.productid INNER JOIN stockinfo d ON d.productid = a.prodictid 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"

Open in new window

0
 

Author Comment

by:sparky74
ID: 22696815
Hi Goodangel

thanks for the code,  It almost works but is pulling back some double results with some wierd values.

I think its because productid and site have multiple results in each column like this

stock info  (this table contains id which is identity)

| site | productid | id |

site1 | 123456 | 1 |
site2 | 123456 | 2 |
site3 | 123456 | 3 |
site1 | 999999 | 8 |
site2 | 999999 | 9 |

priceinfo  (this table contains id which is identity)

| site | productid | id |

site1 | 123456 | 7 |
site2 | 123456 | 8 |
site3 | 123456 | 9 |
site1 | 999999 | 10 |
site2 | 999999 | 11 |

the deliverycost table is setup with - site & productid & id & deliverycost

but only has 1 delivery cost for each site & productid   like this (so I'm guessing maxid might not apply)

| site | productid | id | deliverycost |

site1 | 123456 | 7 | 5.99 |
site2 | 123456 | 8 | 0 |
site3 | 123456 | 9 | 9.99 |

the vouchercode table is setup with - site & productid & id & vouchervalue

but only has 1 vouchervalue for each site & productid , some sites do not have a vouchervalue so will not be in this table

| site | productid | id | vouchervalue |

site1 | 123456 | 7 | 10 |

When Ioaded the code - I got some group by errors because c.vouchervalue, d.instock were not in the group by cause,  so I added them..  Not sure if that screwed it!

I also hav tried changing the inner join using the AND statement  like below , but this then only pulls back the 1 result which contains a vouchervalue

INNER JOIN vouchercode c ON c.productid = a.productid AND c.site=a.site
INNER JOIN stockinfo d ON d.productid = a.productid AND d.site=a.site

I have attached the code again with the change to the group by, there was also a small typo on productid (not moaning) :) just thought it would be better to paste the updated code

your help is much appreciated,  






Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice, (SUM(a.price + b.deliverycost) * c.vouchervalue / 100 ) as VoucherValue, Instock = d.instock FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid INNER JOIN vouchercode c ON c.productid = a.productid INNER JOIN stockinfo d ON d.productid = a.productid  WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='123456789' group by site) group by a.site,a.id,a.updatedatetime, a.productid, a.price, b.deliverycost,c.vouchervalue, d.instock ORDER BY SUM(a.price + b.deliverycost) asc"

Open in new window

0
 

Author Comment

by:sparky74
ID: 22696866
Hi Goodangel

I changed the code slighty again  - and can now see more clearly the problem

I added in - INNER JOIN stockinfo d ON d.productid = a.productid AND d.site=a.site

I'll attach the full code below

The script is now  returning all sites by the productid  and totalling them by Instock & out of stock

this is why I am seeing duplicate results.

The totalprice is summing  all records in the priceinfo table  and splitting/grouping them into  instock and out of stock values

I am only trying to get the last value entered - ie the maxid for each site & productid

I hope this makes more sense


Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice, (SUM(a.price + b.deliverycost) * c.vouchervalue / 100 ) as VoucherValue, Instock = d.instock FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid INNER JOIN vouchercode c ON c.productid = a.productid INNER JOIN stockinfo d ON d.productid = a.productid AND d.site=a.site WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='123456789' group by site) group by a.site,a.id,a.updatedatetime, a.productid, a.price, b.deliverycost,c.vouchervalue, d.instock ORDER BY SUM(a.price + b.deliverycost) asc"

Open in new window

0
 

Author Comment

by:sparky74
ID: 22696978
I got a little further by adding to the WHERE clause

WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='B000FL6NTQ' group by site) AND d.id IN(SELECT MAX(id)  FROM stockinfo  WHERE productid ='B000FL6NTQ' group by site)

Now I've just got a problem with the VOUCHERVALUE  as it is calculating for every site, yet there is only 1 site with a vouchercode.


Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice, (SUM(a.price + b.deliverycost) * c.vouchervalue / 100 ) as VoucherValue, Instock = d.instock FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid INNER JOIN vouchercode c ON c.productid = a.productid INNER JOIN stockinfo d ON d.productid = a.productid AND d.site=a.site WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='B000FL6NTQ' group by site) AND d.id IN(SELECT MAX(id)  FROM stockinfo  WHERE productid ='123456789' group by site)group by  a.productid,a.site,a.id,a.updatedatetime, a.price, b.deliverycost,c.vouchervalue, d.instock ORDER BY SUM(a.price + b.deliverycost) asc"

Open in new window

0
 

Author Comment

by:sparky74
ID: 22697282
Hi

I decided it might be easer to balance out the vouchercode table with the same sites from the other tables and set the value of the vouchervalue to zero.

This seems to do the job.. not sure if its best practice though :)

again I attach the snippet, please let me know if it can be improved


Recordset1.Source = "SELECT a.id, a.updatedatetime, a.productid, a.site, a.price, b.deliverycost, SUM(a.price + b.deliverycost) as totalprice, a.price -(SUM(a.price * c.vouchervalue / 100 )) as VoucherNetPrice, a.price -(SUM(a.price * c.vouchervalue / 100 ))+ b.deliverycost as VoucherValue, Instock = d.instock FROM priceinfo a inner join deliverycost b on a.site=b.site AND a.productid=b.productid INNER JOIN vouchercode c ON c.productid = a.productid AND c.site=a.site INNER JOIN stockinfo d ON d.productid = a.productid AND d.site=a.site WHERE a.id IN(SELECT MAX(id)  FROM priceinfo  WHERE productid ='B000FL6NTQ' group by site) AND d.id IN(SELECT MAX(id)  FROM stockinfo  WHERE productid ='123456' group by site) group by  a.productid,a.site,a.id,a.updatedatetime, a.price, b.deliverycost,c.vouchervalue, d.instock ORDER BY SUM(a.price + b.deliverycost) asc"

Open in new window

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now