Link to home
Start Free TrialLog in
Avatar of TheUndecider
TheUndeciderFlag for United States of America

asked on

Query to Show Users who have Previously Purchased the Same Item Before

Hello,

We like to have a SQL Server query where we could see if someone purchasing an Item has it purchased (or a combo which the item was part of) before.  It should not matter if they've purchased it more than once.  As long as the product has been purchased, it should show up in the query.

For example, if we have these Product and Combos in the Products table:
WidgetA
WidgetB
WidgetC
ComboA = WidgetA + WidgetC
ComboB = WidgetB + WidgetC

And these two users (U229 and U223) are purchasing WidgetC right now, but one of them purchased WidgetC a year ago and the other one purchased ComboB (which WidgetC is part of) they should up in the list. On the other hand, user U221 should not be part of this query because this is the first time he's purchased WidgetA (not pictured in the following Orders record because it should not exist)

Orders Table
OrderID      UserID       Product            PreviousDatePurchased
112             U229      WidgetC               8/1/15
454             U223      ComboB         4/29/16


This is how we'd like this query to show up the results:

UserID      CurrentProduct      PreviousProduct      DatePurchased
U229      WidgetC                    WidgetC                    8/1/15
U223      WidgetC                    ComboB                    4/28/16


One of the reasons I'm having issues with this query is that we have several products and combos they can be part of and that makes it more complicated.  The good thing is that I only have to worry about the users who purchased anything in the past week so  I can limit by that.  However, these orders could have any products.


Any help will be appreciated it.

Thanks.
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Where in your database are you defining that ComboB contains Widget B & C?

Do you write the product as a varchar or is there a productID that you use?
Avatar of TheUndecider

ASKER

Hi Dustin,

Thanks for the feedback.  We do have Product IDs (int) but you can use either in your response.  For the Combos, there is a separate table where we can set Combo components, which also has their own ComboID (int).

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot Dustin,  I will use this information to solve this issue.