Only return records where all sub records match

Question

Only return records where all sub records match

cdh-727733

SSC-Addicted

Points: 454
More actions
June 2, 2010 at 7:38 am

#235880

I am rewriting the delivery / postage section of an e-commerce website and for part of it we need to offer the customer delivery upgrades if applicable.
Each item in my basket is flagged with a "type" that indicates the charge bracket it falls into, and we determine the customers "zone" based on their specified delivery details. I have a table that stores the combination of the zone and type and logs the delivery charges at that level.
As a sub table of this "zone-type" table I have my upgrade costs - this could be for faster or specific delivery slots - the cost is applicable just to the whole order. My basket could contain items if mixed types, but we only ever have a single zone to deal with.
So I need to work through the basket and check what upgrade options are available across the board based on the types in the basket, and then only return the upgrade options that are applicable to all items - i.e. if one item allows a certain upgrade, and one doesn't, we don't show it, I only want the sub records returned where there are exact matches for all records.
My model is this (simplified) :
tblBasket :
ProductID
Qty
Price
ZoneID (will be the same for all in a single basket)
DeliveryTypeID
tblZone :
ZoneID
ZoneName
tblDeliveryType :
DeliveryTypeID
DeliveryTypeName
tblZoneType :
ZoneTypeID
ZoneID
DeliveryTypeID
DeliveryCost
tblUpgrade :
UpgradeID
UpgradeName
tblZoneTypeUpgrade :
ID
ZoneTypeID
UpgradeID
UpgradeCost
So each type within the basket could in theory have different upgrade optiosn available to it, and as I said I only want to see where I have the same UpgradeID's for every record in the basket table - I really hope that makes sense!
Thanks
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Instead of table structure, could you please provide CREATE TABLE and test DATA population scripts to represent your question in a way that would help your potential helpers. Something like

CREATE TABLE #tblBasket

(

ProductID INT

,Qty NUMERIC(10,2)

,Price MONEY

,ZoneID INT

,DeliveryTypeID INT

)

INSERT INTO #tblBasket SELECT 1,10,100.00,99,999

etc.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

In addition, make an attempt to join the tables together in a query.

We are happy to help, but we should not be doing all the work for you.

cdh-727733 SSC-Addicted Points: 454 More actions · Answer 3

Don't worry all sorted - thanks anyway

It was more an approach / theory question than the actual coded answer I was after, but as I said the answer has since dawned on me and I have it working.