Solved

ASP - MS SQL Recordset to Join 4 Tables

Posted on 2008-10-11
5
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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