July 30, 2007 at 11:35 am
I have what 'should' be an easy query but its stumping me so I'm guessing I'm overlooking something simple. I have a data model for Users, Products, Orders and OrderItems (in other words, a User can place one to many orders, an order contains one to many order items and each order item corresponds to a Product PK). I want a query that will return me a list of users that have NOT purchased a given set of products. Since I need to be able to pass in a list of productIds I used dynamic SQL as follows. However it is not working as I want it to...any insight? Much thanx in advance!
CREATE PROCEDURE [dbo].[GetUsersByProductPurchaseExclusion]
@ProductIdsList varchar(1000)
AS
DECLARE @sql varchar(1200)
IF(LEN(@ProductIdsList) >0)
BEGIN
Select @sql = 'SELECT dbo.Users.* FROM dbo.OrderItems INNER JOIN dbo.Orders ON dbo.OrderItems.OrderId = dbo.Orders.Id INNER JOIN dbo.Users ON dbo.Orders.UserId = dbo.Users.Id '
Select @sql = @sql + ' WHERE ProductId NOT IN (' + @ProductIdsList +')'
PRINT @sql
Exec ( @sql)
END
July 30, 2007 at 12:37 pm
To clarify, the dynamic SQL executes fine, my problem which is undoubtedly a logic error. What appears to be happening is this. Say I have User.Id = 1 and that user has purchased ProductId's : 1000, 1001, and 1002. If I call my proc like this:
GetUsersByProductPurchaseExclusion 1001
The proc will correctly filter out the relationship bewteen user.id=1 and orderItems.ProductId=1001 but since it returns true for the other 2 productIds I still get two rows. What I want it to do is say 'User.Id=1 has purchased ProductId=1001 so don't EVER return that user in the result set'
thanx
July 30, 2007 at 12:40 pm
Can you post the printed statement?
July 30, 2007 at 12:44 pm
Can you also post your DDL for the tables involved?
July 30, 2007 at 12:45 pm
SELECT dbo.Users.* FROM dbo.OrderItems INNER JOIN dbo.Orders ON dbo.OrderItems.OrderId = dbo.Orders.Id INNER JOIN dbo.Users ON dbo.Orders.UserId = dbo.Users.Id WHERE ProductId NOT IN (2001)
July 30, 2007 at 1:10 pm
its a very simple and common design:
Users
Id INT (Identity)
FirstName varchar(50)
LastName varchar (50)
etc.
Orders
Id INT (Identity)
UserId INT (FK from Users)
GrandTotal float
etc
Products
Id INT (Identity)
ProductName varchar(100)
etc
OrderItems
Id INT (Identity)
OrderId INT (FK from Orders)
ProductId INT (FK from Products)
Quantity INT
Price float
July 30, 2007 at 1:40 pm
Unless I am missing something, the fix is simple to make. Just move your NOT IN arguments into the JOIN clause.
Select * from sometable inner join sometable2 on 1.id = 2.id and 1.somecolumn not in (some list) inner join sometable3 on 2.id = 3.id (I forgot to copy your code before starting the reply and I was too lazy to go back and get it)
This will filter the product users out of the bunch before they are added to the join, rather than after.
Good Luck!
July 30, 2007 at 1:59 pm
doesn't matter where I put the NOT IN, I get the same results.
The reason is that the join between Users and Orders will return multiple rows (especially with my test data where I've entered dozens of test orders). So assume I have this:
User.Id: 1 Order.Id: 1 OrderItem.ProductId: 1000
User.Id: 1 Order.Id: 2 OrderItem.ProductId: 1001
User.Id: 1 Order.Id: 2 OrderItem.ProductId: 1002
User.Id: 1 Order.Id: 3 OrderItem.ProductId: 1003
If I then pass (1000, 1003) to my proc it will successfully filter those rows out of the result set but because this user also purchased products that are NOT in the exclusion list, I'll still get two rows.
July 30, 2007 at 2:33 pm
My apologies. You are correct. I responded too quickly and with out reading your second post well.
July 30, 2007 at 2:41 pm
It's the end of my workday and I'm about to get a phone call from my boss, so I have to make this quick...
subquery: select only those IDs that test POSITIVE for the exclusion - select ID from table where Product_ID IN (a,b,c...).
Main query: exclude those IDs. Don't reconsider products at all.
Try that... I'll be back online on Wednesday. Good luck.
July 30, 2007 at 2:47 pm
Hopefully this will be more helpful thank my silly reply earlier...
You'll need something like:
SELECT * FROM dbo.Users
WHERE Id NOT IN (
SELECT DISTINCT U.Id
FROM dbo.OrderItems OI
INNER JOIN dbo.Orders O
ON OI.OrderId = O.Id
INNER JOIN dbo.Users U
ON O.UserId = U.Id
WHERE OI.ProductId IN (2001)
)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply