August 15, 2016 at 1:18 pm
I have a table that contains product data and a table that contains what products a user has purchased. I want to have a view where I am able to specify a user id and find out which products of a certain type he has not yet purchased. i would like to return the UserId and the ProdID of the products he has not yet purchased.
Here is the code to create and fill a simplified version of the tables and data.
--------------------------------------------------------------------------------------------------------------
--Create tables
--------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Prod_T](
[ProdID] [nchar](10) NOT NULL,
[ProdTitle] [nchar](30) NULL,
[ProdType] [nchar](10) NULL,
CONSTRAINT [PK_Prod_T] PRIMARY KEY CLUSTERED
(
[ProdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User_Prod_T](
[UserID] [nchar](10) NOT NULL,
[ProdID] [nchar](10) NOT NULL,
CONSTRAINT [PK_User_Prod_T] PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[ProdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------
--Fill the tables
--------------------------------------------------------------------------------------------------------------
insert into Prod_T values('D110','DVD 110','DVD')
insert into Prod_T values('D120','DVD 120','DVD')
insert into Prod_T values('D130','DVD 130','DVD')
insert into Prod_T values('D140','DVD 140','DVD')
insert into Prod_T values('B220','Book 220','BOOK')
insert into Prod_T values('B380','Book 380','BOOK')
Insert into User_Prod_T values('001','D110')
Insert into User_Prod_T values('001','D120')
Insert into User_Prod_T values('001','D130')
Insert into User_Prod_T values('001','B220')
Insert into User_Prod_T values('002','D130')
Insert into User_Prod_T values('002','B380')
Insert into User_Prod_T values('003','B220')
Insert into User_Prod_T values('003','B380')
--------------------------------------------------------------------------------------------------------------
I want to have a view where I am able to specify a user id and find out which DVD products he has not bought yet. How would I write a view to do that?
If I wanted to see which DVD user 002 has not bought yet I would get back something like this
UserID ProdID
002 D110
002 D120
002 D140
Can anyone help me with this?
August 15, 2016 at 1:35 pm
DECLARE @user-id nchar(10)
SET @user-id = '001'
SELECT @user-id AS UserID, p.*, 'Prods This User Hasn''t Purchased' AS Message
FROM Prod_T p
LEFT OUTER JOIN User_Prod_T up ON up.UserID = @user-id AND up.ProdID = p.ProdID
WHERE up.ProdID IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2016 at 2:13 pm
declare @userid nvarchar(10);
select @userid = '001';
select @user-id, ProdID, ProdTitle, ProdType
from Prod_T pt
where not exists (select 1 from User_Prod_T upt where upt.ProdID = pt.ProdID and userID = @userid )
** please check the data type for the columns UserID and ProdID. I think they are better with "Int" instead of varchar.
Good Luck!
August 15, 2016 at 2:21 pm
You can't do it with a view since you want this parameterized. You can use a function or a stored procedure when you want something similar to what would behave like a parameterized view.
A table valued function for this would just be something like (and looks like you want this limited to DVDs based on your results):
CREATE FUNCTION tvf_UnpurchasedDVDByUser
(@UserID nchar(10))
RETURNS TABLE
AS
RETURN
(
SELECT @user-id as UserID,
p.ProdID
FROM Prod_T p
LEFT OUTER JOIN User_Prod_T u
ON u.ProdID = p.ProdID
AND u.UserID = @user-id
WHERE u.ProdID IS NULL
AND p.ProdType = 'DVD'
)
And then just execute like this for User 2:
SELECT * FROM tvf_UnpurchasedDVDByUser('2')
Sue
August 15, 2016 at 2:27 pm
Thanks for the reply everyone. It looks like I can't do it with a view but I got some great ideas from everyone. Thanks again.
August 15, 2016 at 4:05 pm
msmith-880862 (8/15/2016)
Thanks for the reply everyone. It looks like I can't do it with a view but I got some great ideas from everyone. Thanks again.
Just a sidebar on this... Sue-H wrote a nice, high-performance iTVF ("inline Table Valued Function") above instead of a more slothful mTVF (multi-statement Table Valued Function), which some folks will refer to as a "parameterized view". They're pretty easy to spot because an iTVF can't have the word BEGIN in it. It has to be written like a view for it to work.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2016 at 7:34 pm
Another approach that uses an ITVF, only it uses the EXCEPT set operator instead:
if object_id('dbo.NotPurchased', 'if') is not null drop function dbo.NotPurchased;
go
CREATE FUNCTION dbo.NotPurchased(@UserID Nchar(10)) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
WITH cteUnpurchased AS (
SELECT ProdID
FROM dbo.Prod_T
EXCEPT
SELECT ProdID
FROM dbo.User_Prod_T
WHERE UserID = @user-id
)
SELECT p.ProdID, p.ProdTitle, p.ProdType
FROM dbo.Prod_T p
CROSS APPLY cteUnpurchased u
WHERE p.ProdID = u.ProdID
);
go
SELECT *
FROM dbo.NotPurchased(N'001') np
ORDER BY ProdID;
You can modify it to include only products of specific types or add another parameter to limit the type. You may not be able to do it with a view because of the parameter, but you can come close with a function. Jeff has already offered the word of caution - Avoid the dreaded MTVF.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply