May 22, 2009 at 11:38 am
Hi,
I have an existing online store database (SQL2005) which is being upgraded. The key tables for this topic are
BASKETS -- (PK: BASKET_ID)
BASKETITEMS -- (PK: BITEM_ID) includes BITEM_PRICE, BITEM_QTY and a FK BITEM_BASKETID
BASKET_ITEM_ATTRIBUTES -- (PK: BIATTR_ID)
PROD_OPTIONS -- (PK: PROD_OPTION_ID) e.g. 'Colour', 'Size' etc.
PROD_OPTION_CHOICES -- (PK: PROD_OPTCHOICE_ID) PROD_OPTCHOICE_VALUE: Blue,Grn,Red,Small,Medium,Large etc.
PROD_ATTRIBUTES -- (PK: PROD_ATTR_ID) links up specific options and choices to products
Basically... what I am trying to do is provide a script that will validate the content of a user's basket at specific events, e.g. when they visit the My Basket page; when they go through the checkout etc.
The idea of this is that if the store administrators change the options and or choices that apply to a product that is already in a customer's basket, a message will be generated to the customer to warn that their specific item configuration is no longer available.
For example....
- a customer views a shirt which is available in S/M/L and red/blue/green
- the customer adds a blue medium shirt to their basket and they go away for x days without making a purchase
- whilst they are away, the admin removes the blue variant from this shirt (now only available in green or red)
- the customer returns and goes to the checkout; the query should invalidate the particular combination of options/choices so that I can code in a warning message and ask them to re-select an alternative configuration
I am also (deliberately) using the same query to check
- if there have been any price changes to the base product price since the customer added the item to their basket
- if there have been any price changes to the specific variants selected (e.g. has cost of green gone up?)
- if the product is still available (by checking PROD_STATUS where 3=live)
So... apologies to those of you who can be bothered to read such a large query; I'm not suggesting this is the most efficient query by any means, I'm just trying to get the data I want first.. and the filtering out of invalid rows is 'partly working' towards the bottom.
The filtering out works if ALL of the option-choices chosen by the user for a specific product are no longer available against the product. However, if one of the option-choices is still available, the row still gets included.
Where I've got to so far..
------------------------------------------------------------------------------------------------------------
SELECT BI.BITEM_ID,
P.PROD_TITLE,
P.PROD_PRICEPU + SUM(VARIANT_PRICE_ADJUSTMENTS.PRICE_CHANGE), --see below
BI.BITEM_QTY,
BI.BITEM_QTY * (P.PROD_PRICEPU + SUM(VARIANT_PRICE_ADJUSTMENTS.PRICE_CHANGE)) AS BITEM_LINE_PRICE,
PO.PROD_OPTION_DEFAULT_BASKET_LABEL, --e.g. 'Colour:' or 'Size:'
POC.PROD_OPTCHOICE_VALUE, --e.g. 'Blue' or 'Medium'
P.PROD_STATUS,
(SELECT COUNT(*) FROM PROD_ATTRIBUTES PA
WHERE PA.PROD_ATTR_CHOICE_ID = BIA.BIATTR_CHOICE_ID
AND PA.PROD_ATTR_OPTION_ID = BIA.BIATTR_OPTION_ID
AND PA.PROD_ATTR_PROD_ID=BI.BITEM_PRODID) AS 'OptionChoiceAvail',
'PriceChange' =
CASE
WHEN (P.PROD_PRICEPU + SUM(VARIANT_PRICE_ADJUSTMENTS.PRICE_CHANGE)) = BI.BITEM_PRICE THEN 'same price'
WHEN (P.PROD_PRICEPU + SUM(VARIANT_PRICE_ADJUSTMENTS.PRICE_CHANGE)) > BI.BITEM_PRICE THEN 'increased price'
WHEN (P.PROD_PRICEPU + SUM(VARIANT_PRICE_ADJUSTMENTS.PRICE_CHANGE)) < BI.BITEM_PRICE THEN 'cheaper price'
END
FROM BASKETITEMS BI
LEFT OUTER JOIN BASKET_ITEM_ATTRIBUTES BIA ON BI.BITEM_ID = BIA.BIATTR_BITEM_ID
LEFT OUTER JOIN PROD_OPTIONS PO ON BIA.BIATTR_OPTION_ID = PO.PROD_OPTION_ID
LEFT OUTER JOIN PROD_OPTION_CHOICES POC ON BIA.BIATTR_CHOICE_ID = POC.PROD_OPTCHOICE_ID
LEFT OUTER JOIN (
SELECT PA2.PROD_ATTR_PROD_ID, SUM(PA2.PROD_ATTR_COST_ADJ) AS PRICE_CHANGE
FROM PROD_ATTRIBUTES PA2
LEFT OUTER JOIN BASKET_ITEM_ATTRIBUTES BIA on
PA2.PROD_ATTR_OPTION_ID = BIA.BIATTR_OPTION_ID
AND PA2.PROD_ATTR_CHOICE_ID = BIA.BIATTR_CHOICE_ID
GROUP BY PA2.PROD_ATTR_PROD_ID, PA2.PROD_ATTR_OPTION_ID, PA2.PROD_ATTR_CHOICE_ID
) AS VARIANT_PRICE_ADJUSTMENTS on BI.BITEM_PRODID = VARIANT_PRICE_ADJUSTMENTS.PROD_ATTR_PROD_ID,
PRODUCTS P
WHERE BI.BITEM_BASKETID = 20153
AND BI.BITEM_PRODID = PROD_ID
--this is where I am trying to filter out any products where the customer's choice of options is no longer valid
AND BI.BITEM_ID IN (
SELECT DISTINCT BI.BITEM_ID
FROM BASKETITEMS BI
LEFT OUTER JOIN BASKET_ITEM_ATTRIBUTES BIA on BI.BITEM_ID = BIA.BIATTR_BITEM_ID
INNER JOIN PROD_ATTRIBUTES PA on (BIA.BIATTR_OPTION_ID = PA.PROD_ATTR_OPTION_ID
AND BIA.BIATTR_CHOICE_ID = PA.PROD_ATTR_CHOICE_ID
AND BI.BITEM_PRODID = PA.PROD_ATTR_PROD_ID)
WHERE BI.BITEM_BASKETID = 20153
)
GROUP BY BI.BITEM_ID, BI.BITEM_PRODID, P.PROD_TITLE, BI.BITEM_QTY, P.PROD_PRICEPU, BI.BITEM_PRICE, PO.PROD_OPTION_DEFAULT_BASKET_LABEL,
POC.PROD_OPTCHOICE_VALUE, P.PROD_STATUS, BI.BITEM_ROWNUMBER, BIA.BIATTR_CHOICE_ID, BIA.BIATTR_OPTION_ID
ORDER BY P.PROD_STATUS DESC, BI.BITEM_ROWNUMBER
----------------------------------------------------------------------------------------------------------------
The OptionChoiceAvail field is not necessarily going to be in the final query but I am currently using it for reference.
So again.. sorry that it's such a long query.. but ... how do I get it to pull back the data for those BASKETITEMS which still have valid combinations of options and choices and/or exclude those that are no longer valid?
To expand on that - I am looking to identify rows in BASKETITEMS which have entries in BASKET_ITEM_ATTRIBUTES (meaning that the product has options and choices) where one or more of those option/choice combinations that the user has selected is no longer in PROD_ATTRIBUTES. I have thought of using HAVING or EXISTS but can't seem to get the query right.
The db structure is fairly highly normalised so feel free to shout if you need further info.
Thank you for taking the time to read this - it's been frustrating me so I'll look forward to any tips you can share.
Glyn
May 22, 2009 at 12:19 pm
Glyn,
You will get much clearer replies, and tested solutions faster, if you can take the time to set up the problem with some sample create table scripts and data as explained here.[/url] Trust me, it will win you friends among the volunteers who try to help out.
That said, generally speaking if you select from table B and left join to table B, you will know that table B doesn't have a match if the primary key for table B comes back null.
declare @tableA table (color varchar(10))
declare @tableB table (color varchar(10))
--
insert into @tableA
select 'Red' Union All
select 'Green' Union all
select 'Blue'
--
insert into @tableB
select 'Red' union all
select 'Blue'
select a.color
from @tableA a
left join @tableb b on a.color = b.color
where b.color is null
If I understand you correctly, product_attributes is table B. The join is more complex, but you want to identify any attributes which no longer exist in the product_attributes table.
INNER JOIN PROD_ATTRIBUTES PA on (BIA.BIATTR_OPTION_ID = PA.PROD_ATTR_OPTION_ID
AND BIA.BIATTR_CHOICE_ID = PA.PROD_ATTR_CHOICE_ID
AND BI.BITEM_PRODID = PA.PROD_ATTR_PROD_ID)
Try turning that into a LEFT JOIN and adding the following WHERE clause:
WHERE PA.PROD_ATTR_ID is null
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 22, 2009 at 2:05 pm
Hi Bob,
Thank you for reviewing the query. Points taken on board re information I should be giving. I will remember this and endeavour to post that info if I have any further queries.
Regarding your answer, I have used this and it partially solved the problem. I've then switched
"AND BI.BITEM_ID IN (
SELECT DISTINCT BI.BITEM_ID "
to
"AND BI.BITEM_ID NOT IN (
SELECT DISTINCT BI.BITEM_ID "
and it appears to work a treat - which I'm delighted about. I have some further testing to carry out tomorrow to double-check that this works in the various contexts - but a big thank you in advance... Fingers crossed!! 🙂
Best regards,
Glyn
May 22, 2009 at 2:25 pm
For whatever assistance I actually rendered, you are welcome. Hope it works out for you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 22, 2009 at 3:31 pm
Be careful using NOT IN - there's a small little gotcha that you have to be aware of. If just one item can be returned as NULL, it is going to eliminate all results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply