Hi all.
Firstly: please only respond if you have a working computer with SQL Server installed
Secondly: please only respond if you have good experience and knowledge of T-SQL
Not having much luck so hopefully someone can understand and can help without the endless back and forth and not being well equipped!
So, my SQL is not as good as what it used to be!
Basically I am trying to create a query that will pull back records for these fields:
OrderPlacedOn
CourseNumber
Title (Course)
Cost (Course)
AccessCodeID (User order history transaction)
AccessCode
CategoryName (Category)
State (User)
The purpose of the query is to view records that track what access codes was used to purchase what courses. If it is a paypal purchase then we also need to know that too (Paypal purchase is determined on if the AccessCodeID is NULL in the user order history transaction table)
Does this make sense?
I tried this query but I get back what appears to be duplicates or records that are associated with access codes that the individual did not use to purchase at all.
SELECT u.UserID, u.Firstname, u.Lastname, u.EmailAddress, uoh.OrderPlacedOn as [Purchase Date], /ucc.DateCreated AS [Date Passed],/ c.CourseNumber, c.Title, c.Cost AS [Course Price],
uoht.AccessCodeID AS [Purchase Type], ac.AccessCode, cat.CategoryName AS [License Type], u.[State]
FROM UserOrderHistory uoh
INNER JOIN UserOrderHistoryTransaction uoht ON uoht.UserOrderHistoryID = uoh.ID
INNER JOIN Users u ON u.UserID = uoh.UserID
INNER JOIN Category cat ON cat.CategoryID = u.ProfessionalLicenseType
--LEFT OUTER JOIN UserCourseCertificate ucc ON ucc.UserID = u.UserID
INNER JOIN UserRegisteredCourses urc ON urc.UserID = u.UserID
INNER JOIN Course c ON c.CourseID = urc.CourseID
LEFT OUTER JOIN CourseAccessCode ca ON ca.CourseID = c.CourseID
INNER JOIN AccessCodes ac on ac.ID = ca.AccessCodeID
GROUP BY u.UserID, u.Firstname, u.Lastname, u.EmailAddress, uoh.OrderPlacedOn , /ucc.DateCreated ,/ c.CourseNumber, c.Title, c.Cost ,
uoht.AccessCodeID, cat.CategoryName , u.[State], ac.AccessCode
ORDER BY u.UserID, c.CourseNumber
Here is my schema:
/****** Object: Table [dbo].[AccessCodes] Script Date: 22/08/2019 08:50:35 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccessCodes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[Active] [bit] NOT NULL,
[AccessCode] nvarchar NOT NULL,
[ActiveFrom] [datetime] NULL,
[ActiveTo] [datetime] NULL,
[HREmail] nvarchar NULL,
[PercentDiscount] [int] NOT NULL,
CONSTRAINT [PK_AccessCodes] PRIMARY KEY CLUSTERED
(
[ID] 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
/ Object: Table [dbo].[Category] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] nvarchar NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] 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
/ Object: Table [dbo].[Course] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[CourseNumber] nvarchar NOT NULL,
[Title] nvarchar NOT NULL,
[CategoryID] [int] NOT NULL,
[Description] nvarchar NOT NULL,
[Cost] [money] NOT NULL,
[IsAARCApproved] [bit] NOT NULL,
[Status] [tinyint] NOT NULL,
[ProviderID] [int] NOT NULL,
[ContractHours] [decimal](16, 2) NOT NULL,
[CertificateFee] [money] NOT NULL,
[Disclaimer] nvarchar NULL,
[RequireSurveyCompletion] [bit] NOT NULL,
[RequireCertificateAccessCode] [bit] NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] 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
/ Object: Table [dbo].[CourseAccessCode] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CourseAccessCode](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [int] NOT NULL,
[AccessCodeID] [int] NOT NULL,
CONSTRAINT [PK_CourseAccessCode] PRIMARY KEY CLUSTERED
(
[ID] 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
/ Object: Table [dbo].[UserOrderHistory] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserOrderHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateAdded] [datetime] NOT NULL,
[UserID] [int] NOT NULL,
[OrderPlacedOn] [datetime] NOT NULL,
[CourseID] [int] NOT NULL,
[CourseTitle] nvarchar NOT NULL,
[CourseNumber] nvarchar NOT NULL,
[TotalCost] [money] NOT NULL,
[ProviderID] [int] NOT NULL,
[ProviderName] nvarchar NOT NULL,
[ProviderNumber] nvarchar NOT NULL,
[ProviderEmail] nvarchar NOT NULL,
[UserEmailAddress] nvarchar NOT NULL,
[CourseSubscriptionID] [int] NULL,
CONSTRAINT [PK_UserOrderHistory] PRIMARY KEY CLUSTERED
(
[ID] 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
/ Object: Table [dbo].[UserOrderHistoryTransaction] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserOrderHistoryTransaction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UserID] [int] NOT NULL,
[ProviderTransactionID] nvarchar NOT NULL,
[TotalCost] [money] NOT NULL,
[UserOrderHistoryID] [int] NOT NULL,
[AccessCEReference] nvarchar NOT NULL,
[AccessCodeID] [int] NULL,
[AuthCode] nvarchar NULL,
[ReasonCode] nvarchar NULL,
[Decision] nvarchar NULL,
[ResponseString] nvarchar NULL,
CONSTRAINT [PK_UserOrderHistoryTransaction] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[UserRegisteredCourses] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRegisteredCourses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[DateOfCompletion] [datetime] NULL,
[ConfirmationCode] nvarchar NULL,
[Status] [tinyint] NOT NULL,
[AffirmationSigned] [bit] NULL,
[PurchasedCertificate] [bit] NOT NULL,
[CourseSubscriptionExpiration] [datetime] NULL,
[CourseSubscriptionID] [int] NULL,
CONSTRAINT [PK_UserRegisteredCourses] PRIMARY KEY CLUSTERED
(
[ID] 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
/ Object: Table [dbo].[Users] Script Date: 22/08/2019 08:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[DateOfRegistration] [datetime] NOT NULL,
[Firstname] nvarchar NOT NULL,
[Lastname] nvarchar NOT NULL,
[Password] nvarchar NOT NULL,
[IsActive] [bit] NOT NULL,
[EmailAddress] nvarchar NOT NULL,
[PhoneNumber] nvarchar NOT NULL,
[RegistrationKey] nvarchar NULL,
[ProfLicenseNumber] varchar NULL,
[AARC] varchar NULL,
[State] varchar NULL,
[SecurityQuestion] nvarchar NULL,
[SecurityQuestionAnswer] nvarchar NULL,
[NewsletterSub] [bit] NOT NULL,
[ProfessionalLicenseType] [int] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] 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
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_PercentDiscount] DEFAULT ((0)) FOR [PercentDiscount]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_IsActive] DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_Cost] DEFAULT ((0)) FOR [Cost]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_IsAARCApproved] DEFAULT ((0)) FOR [IsAARCApproved]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_ContactHours] DEFAULT ((0)) FOR [ContractHours]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_CertificateFee] DEFAULT ((0)) FOR [CertificateFee]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_RequireSurveyCompletion] DEFAULT ((0)) FOR [RequireSurveyCompletion]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_RequireCertificateAccessCode] DEFAULT ((0)) FOR [RequireCertificateAccessCode]
GO
ALTER TABLE [dbo].[UserOrderHistory] ADD CONSTRAINT [DF_UserOrderHistory_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[UserOrderHistory] ADD CONSTRAINT [DF_UserOrderHistory_TotalCost] DEFAULT ((0)) FOR [TotalCost]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] ADD CONSTRAINT [DF_UserOrderHistoryTransaction_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] ADD CONSTRAINT [DF_UserOrderHistoryTransaction_TotalCost] DEFAULT ((0)) FOR [TotalCost]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD CONSTRAINT [DF_UserRegisteredCourses_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD DEFAULT ((0)) FOR [AffirmationSigned]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD CONSTRAINT [DF_UserRegisteredCourses_PurchasedCertificate] DEFAULT ((0)) FOR [PurchasedCertificate]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_DateOfRegistration] DEFAULT (getdate()) FOR [DateOfRegistration]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsActive] DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_NewsletterSub] DEFAULT ((1)) FOR [NewsletterSub]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Category]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Provider] FOREIGN KEY([ProviderID])
REFERENCES [dbo].[Provider] ([ID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Provider]
GO
ALTER TABLE [dbo].[CourseAccessCode] WITH CHECK ADD CONSTRAINT [FK_CourseAccessCode_AccessCodes] FOREIGN KEY([AccessCodeID])
REFERENCES [dbo].[AccessCodes] ([ID])
GO
ALTER TABLE [dbo].[CourseAccessCode] CHECK CONSTRAINT [FK_CourseAccessCode_AccessCodes]
GO
ALTER TABLE [dbo].[CourseAccessCode] WITH CHECK ADD CONSTRAINT [FK_CourseAccessCode_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseAccessCode] CHECK CONSTRAINT [FK_CourseAccessCode_Course]
GO
ALTER TABLE [dbo].[UserOrderHistory] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistory_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserOrderHistory] CHECK CONSTRAINT [FK_UserOrderHistory_Users]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_AccessCodes] FOREIGN KEY([AccessCodeID])
REFERENCES [dbo].[AccessCodes] ([ID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_AccessCodes]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_UserOrderHistory] FOREIGN KEY([UserOrderHistoryID])
REFERENCES [dbo].[UserOrderHistory] ([ID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_UserOrderHistory]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_Users]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_Course]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_CourseSubscription] FOREIGN KEY([CourseSubscriptionID])
REFERENCES [dbo].[CourseSubscription] ([ID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_CourseSubscription]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_Users]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Users] FOREIGN KEY([ProfessionalLicenseType])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Users]
GO