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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy