Left Outer Join, Multi Tables, To Populate Checkboxes in Classic ASP Application

Question

Post reply

Left Outer Join, Multi Tables, To Populate Checkboxes in Classic ASP Application

cajun_sql

SSCommitted

Points: 1842
More actions
April 25, 2014 at 9:40 am

#290588

Hi,
I've been working with several tables in sql server 2008, and the data is used in a Classic ASP application.
The following query (please see below) has been used to populate an 'update' page with checkboxes. The first table (KSA), populates the checkboxes, while the second table (KSA_Out) provides the checkboxes that the user checked. There is a set of these KSA checkboxes for each Objective, and the problem I'm having is that I would like for the KSA Left Join to still populate the form's checkboxes, even if there are no matches in the joined tables.
The idea behind the application is this:
There is a page with a table: a row for each objective, and a column for each outcome.
The table is populated by the Objectives table and the Outcomes table, with an "Add/Edit" link in each cell, and a URL with Querystring that passes the ObjectiveID and the OutcomeID to the next page, which updates the Objective/Outcome combination.
On the update page, there is a query that populates a form with checkboxes. The KSA table populates the list items, while the OutcomesKSA table provides any previously user-entered data, in the form of a checked checkbox (the OutcomesKSA.KSA_Value field, which is boolean). The ObjOut table is a junction table that ties in the Objectives and Outcomes tables.
In the application, I would like to get only the data for a certain Objective/Outcome combination, if the user-entered checkbox data exists (the OutcomesKSA.KSA_Value field, which is boolean), yet still get only the form list from the KSA table, if no data has been entered in the OutcomesKSA table.
Currently, nothing is populated, with the query I'm using (please see below) .
Thanks for any help in this.
Kind Regards,
Louis
Here's how the data would appear, from table to table, to show how the data is dispersed and related (I've only included PKs and FKs, to show their
relationships):
========================
Objectives table
ID
5
Outcomes table
ID
4
ObjOut table
ID|ObjectiveID|OutcomeID
1|5|4
OutcomesKSA table
ID|ObjOutID|KSA_ID
2|1|1
3|1|2
4|1|3
KSA table
ID
1
2
3
The query:
==========
SELECT
KSA.ID as KSA_ID, KSA.KSA_Version, KSA.KSA_Sort_Order_Number,
KSA.KSA_Outcome_Number, KSA.KSA_Category as KSA_KSA_Category,
KSA.KSA_Category_Sub_Num, KSA.KSA_Category_Sub_Num_Descr,
KSA.KSA_Category_Sub_Num_Sub_Alpha, KSA.KSA_Category_Sub_Num_Sub_Alpha_Descr,
KSA.KSA_ID as KSA_KSA_ID, KSA.KSA_Descr_Combined as KSA_KSA_Descr_Combined,
KSA.LastUpdate, KSA.Date_Created,
OutcomesKSA.KSA_ID as OutcomesKSA_KSA_ID,OutcomesKSA.KSA_Value as OutcomesKSA_KSA_Value,OutcomesKSA.ObjOutID,
ObjOut.ObjectiveID, ObjOut.OutcomeID,
Objectives.ID as Obj_Obj_ID
FROM
KSA
LEFT OUTER JOIN
OutcomesKSA
ON
KSA.ID=OutcomesKSA.KSA_ID
INNER JOIN
ObjOut
ON
OutcomesKSA.ObjOutID=ObjOut.ID
INNER JOIN
Objectives
ON
ObjOut.ObjectiveID=Objectives.ID
WHERE
KSA.KSA_Outcome_Number ='1'
AND
KSA.KSA_Category ='k'
AND
Objectives.ID ='29'
ORDER BY KSA.KSA_Sort_Order_Number ASC;
The tables:
============
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Objectives](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [int] NOT NULL,
[Objective] [varchar](max) NULL,
[LastUpdate] [datetime] NULL,
[Date_Created] [datetime] NULL,
CONSTRAINT [PK_Objectives] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Objectives] WITH NOCHECK ADD CONSTRAINT [FK_Objectives_Courses] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Courses] ([CourseID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Objectives] CHECK CONSTRAINT [FK_Objectives_Courses]
GO
ALTER TABLE [dbo].[Objectives] ADD CONSTRAINT [DF_Objectives_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
GO
=============
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Outcomes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OutcomeGroup] [varchar](50) NULL,
[OutcomeType] [varchar](50) NULL,
[OutcomeNumber] [int] NULL,
[OutcomeName] [varchar](500) NULL,
[OutcomeDescription] [varchar](max) NULL,
[OutcomeVersionYear] [varchar](50) NULL,
[OutcomeVersionSemester] [varchar](50) NULL,
[LastUpdate] [datetime] NULL,
[Date_Created] [datetime] NULL,
CONSTRAINT [PK_Outcomes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Outcomes] ADD CONSTRAINT [DF_Outcomes_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
GO
==========
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ObjOut](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ObjectiveID] [int] NULL,
[OutcomeID] [int] NULL,
[LastUpdate] [datetime] NULL,
[Date_Created] [datetime] NULL,
CONSTRAINT [PK_ObjOut] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ObjOut] WITH CHECK ADD CONSTRAINT [FK_ObjOut_Objectives] FOREIGN KEY([ObjectiveID])
REFERENCES [dbo].[Objectives] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ObjOut] CHECK CONSTRAINT [FK_ObjOut_Objectives]
GO
ALTER TABLE [dbo].[ObjOut] WITH CHECK ADD CONSTRAINT [FK_ObjOut_Outcomes] FOREIGN KEY([OutcomeID])
REFERENCES [dbo].[Outcomes] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ObjOut] CHECK CONSTRAINT [FK_ObjOut_Outcomes]
GO
ALTER TABLE [dbo].[ObjOut] ADD CONSTRAINT [DF_ObjOut_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
GO
===========
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OutcomesKSA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ObjOutID] [int] NOT NULL,
[KSA_ID] [int] NULL,
[KSA_Value] [bit] NULL,
[LastUpdate] [datetime] NULL,
[Date_Created] [datetime] NULL,
CONSTRAINT [PK_OutcomesKSA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OutcomesKSA] WITH CHECK ADD CONSTRAINT [FK_OutcomesKSA_ObjOut] FOREIGN KEY([ObjOutID])
REFERENCES [dbo].[ObjOut] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OutcomesKSA] CHECK CONSTRAINT [FK_OutcomesKSA_ObjOut]
GO
ALTER TABLE [dbo].[OutcomesKSA] ADD CONSTRAINT [DF_OutcomesKSA_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
=========================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KSA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KSA_Version] [varchar](50) NULL,
[KSA_Sort_Order_Number] [int] NULL,
[KSA_Outcome_Number] [varchar](50) NULL,
[KSA_Outcome_Number_Sort] [varchar](50) NULL,
[KSA_Category] [char](1) NULL,
[KSA_Category_Sub_Num] [varchar](50) NULL,
[KSA_Category_Sub_Num_Sort] [varchar](50) NULL,
[KSA_Category_Sub_Num_Descr] [varchar](max) NULL,
[KSA_Category_Sub_Num_Sub_Alpha] [char](1) NULL,
[KSA_Category_Sub_Num_Sub_Alpha_Descr] [varchar](1000) NULL,
[KSA_ID] [char](8) NULL,
[KSA_ID_Sort] [char](8) NULL,
[KSA_Descr_Combined] [varchar](max) NULL,
[LastUpdate] [datetime] NULL,
[Date_Created] [datetime] NULL,
CONSTRAINT [PK_KSA] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KSA] ADD CONSTRAINT [DF_KSA_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
GO
==========================

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

cajun_sql SSCommitted Points: 1842 More actions · Answer 1

Thanks - I wanted to share the solution, thanks to user AlwaysLoadingData on StackExchange:

SELECT

KSA.ID as KSA_ID

,KSA.KSA_Version

,KSA.KSA_Sort_Order_Number

,KSA.KSA_Outcome_Number

,KSA.KSA_Category as KSA_KSA_Category

,KSA.KSA_Category_Sub_Num

,KSA.KSA_Category_Sub_Num_Descr

,KSA.KSA_Category_Sub_Num_Sub_Alpha

,KSA.KSA_Category_Sub_Num_Sub_Alpha_Descr

,KSA.KSA_ID as KSA_KSA_ID

,KSA.KSA_Descr_Combined as KSA_KSA_Descr_Combined

,KSA.LastUpdate

,KSA.Date_Created

,OutcomesKSA.KSA_ID as OutcomesKSA_KSA_ID

,OutcomesKSA.KSA_Value as OutcomesKSA_KSA_Value

,OutcomesKSA.ObjOutID

,ObjOut.ObjectiveID

,ObjOut.OutcomeID

,Objectives.ID as Obj_Obj_ID

FROM

KSA

LEFT OUTER JOIN (

OutcomesKSA

INNER JOIN ObjOut

ON OutcomesKSA.ObjOutID = ObjOut.ID

INNER JOIN Objectives

ON ObjOut.ObjectiveID = Objectives.ID

) ON KSA.ID = OutcomesKSA.KSA_ID

AND Objectives.ID = 29 --filter

WHERE

KSA.KSA_Outcome_Number = '1'

AND KSA.KSA_Category = 'k'

ORDER BY KSA.KSA_Sort_Order_Number;