Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Left Outer Join, Multi Tables, To Populate Checkboxes in Classic ASP Application Expand / Collapse
Author
Message
Posted Friday, April 25, 2014 9:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:12 PM
Points: 907, Visits: 263
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









==========================
Post #1565153
Posted Monday, April 28, 2014 1:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:12 PM
Points: 907, Visits: 263
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;
Post #1565690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse