January 31, 2010 at 1:50 pm
I need help creating a Stored procedure to display records from one table regardless if a secondary key is present in a cross reference table. First let me post some code for creating the tables and for inserting some example date:
CREATE TABLE [dbo].[TRAINING] (
[TRAINING_ID] int IDENTITY(1,1) NOT NULL,
[TRAINING_NAME] varchar(50) NULL,
[TRAINING_DESC] text NULL,
[MANDATORY] char(1) NULL,
[DISPLAY_STATUS]varchar(8) NULL CONSTRAINT [DF_TRAINING_DISPLAY_STATUS] DEFAULT ('ACTIVE'),
CONSTRAINT [PK_TRAINING] PRIMARY KEY([TRAINING_ID])
)
GO
CREATE TABLE [dbo].[PARENT_GUARDIAN] (
[PG_ID] int IDENTITY(150,1) NOT NULL,
[PG_FNAME] nvarchar(25) NOT NULL,
[PG_LNAME] nvarchar(25) NOT NULL,
CONSTRAINT [PK_PARENT_GUARDIAN] PRIMARY KEY([PG_ID])
)
GO
CREATE TABLE [dbo].[xref_PG_TRAINING] (
[PG_ID] int NOT NULL,
[TRAINING_ID] int NOT NULL,
[DATE_COMPLETED]datetime NOT NULL
)
GO
INSERT INTO PARENT_GUARDIAN (PG_FNAME, PG_LNAME)
VALUES ('Dean','Barker')
INSERT INTO PARENT_GUARDIAN (PG_FNAME, PG_LNAME)
VALUES ('Frank','Purdue')
INSERT INTO PARENT_GUARDIAN (PG_FNAME, PG_LNAME)
VALUES ('John','Cummings')
GO
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('Youth Protection','Blah Blah','Yes')
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('Fast Start: Den Leader','Blah Blah','Yes')
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('Fast Start: Cubmaster','Blah Blah','Yes')
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('Fast Start: Tiger Cub Den Leaders','Blah Blah','No')
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('Fast Start: Webelos Den Leaders','Blah Blah','No')
INSERT INTO TRAINING (TRAINING_NAME,TRAINING_DESC, MANDATORY)
VALUES ('This is Scouting','Blah Blah','No')
GO
-- PARENT_GUARDIAN DEAN PARKER HAS COMPLETED ALL POSSIBLE TRAINING
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,1,'1/1/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,2,'1/3/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,3,'1/4/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,4,'1/6/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,5,'1/6/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,6,'1/18/2010')
-- PARENT_GUARDIAN FRANK PURDUE HAS COMPLETED ONLY 3 CLASSES
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,1,'1/4/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,2,'1/7/2010')
INSERT INTO xref_PG_TRAINING (PG_ID, TRAINING_ID, DATE_COMPLETED)
VALUES (1,3,'1/10/2010')
-- PARENT_GUARDIAN JOHN CUMMINGS HAS NOT COMPLETED ANY CLASSES
I need to create a stored procedure that will produce the following results based on 1 variable - @PG_ID
SELECT TRAINING.TRAINING_NAME, TRAINING.MANDATORY, xref_PG_TRAINING.DATE_COMPLETED WHERE xref_PG_TRAINING.PG_ID = @PG_ID
For Example, if @PG_ID = 1 Then the following Results would be displayed
Results for Dean Parker PG_ID = 1
1 Youth Protection | Yes | 1/1/2010
2 Fast Start: Den Leader | Yes | 1/3/2010
3 Fast Start: Cubmaster | Yes | 1/4/2010
4 Fast Start: Tiger Cub Den Leaders | No | 1/6/2010
5 Fast Start: Webelos Den Leaders | No | 1/6/2010
6 This is Scouting | No | 1/18/2010
However if @PG_ID = 2 Then the following Results would be displayed
Results for Dean Parker PG_ID = 2
1 Youth Protection | Yes | 1/4/2010
2 Fast Start: Den Leader | Yes | 1/7/2010
3 Fast Start: Cubmaster | Yes | 1/10/2010
4 Fast Start: Tiger Cub Den Leaders | No | NULL
5 Fast Start: Webelos Den Leaders | No | NULL
6 This is Scouting | No | NULL
Notice I still want to display the all Possible Training regradless if PG_ID is present in the Cross Reference Table dbo.xref_PG_TRAINING
And if @PG_ID = 3 Then the following Results would be displayed
Results for John Cummings PG_ID = 3
1 Youth Protection | Yes | NULL
2 Fast Start: Den Leader | Yes | NULL
3 Fast Start: Cubmaster | Yes | NULL
4 Fast Start: Tiger Cub Den Leaders | No | NULL
5 Fast Start: Webelos Den Leaders | No | NULL
6 This is Scouting | No | NULL
February 1, 2010 at 1:36 am
If i understand you correctly you just need to simply "outer join"
February 1, 2010 at 10:25 pm
What it comes down to is that I want to display all Training Records from the TRAINING table, and but for those training classes that an individual has taken I would like to display the date the class was taken. If they haven't taken the class then I will display null or text that says "Not Taken" Most likely a Case Statement
February 2, 2010 at 12:49 am
Dave is correct. Use an outer join and don't forget to put the filter on PG_ID in the join condition, not in your WHERE clause!
If you want to replace the NULL for DATE_COMPLETED with text, you can use a case stamement, but ISNULL or COALESCE will work too. You'll have to convert your datetime to a (var)char first though.
February 2, 2010 at 7:38 am
NO IDEA How to do this. Here is what I have so far and I know this is not right considering the where clause is only allowing me to see those records that exist in the TRAINING table where PG_ID is in xref_PG_TRAINING table. I need all records displayed from the TRAINING table . Sorry but Im not getting it. I do understand how outter joins work however Im not getting the rest.
SELECT
*
FROM
[dbo].[TRAINING] A
LEFT OUTER JOIN [dbo].[xref_PG_TRAINING] B
ON A.[TRAINING_ID] = B.[TRAINING_ID]
WHERE B.PG_ID = 1
February 2, 2010 at 7:45 am
netguykb (2/2/2010)
NO IDEA How to do this. Here is what I have so far and I know this is not right considering the where clause is only allowing me to see those records that exist in the TRAINING table where PG_ID is in xref_PG_TRAINING table. I need all records displayed from the TRAINING table . Sorry but Im not getting it. I do understand how outter joins work however Im not getting the rest.
SELECT*
FROM
[dbo].[TRAINING] A
LEFT OUTER JOIN [dbo].[xref_PG_TRAINING] B
ON A.[TRAINING_ID] = B.[TRAINING_ID]
AND B.PG_ID = 1
WHERE B.PG_ID = 1
As FROM is evaluated before WHERE, try the above alteration.
February 2, 2010 at 7:48 am
Before I had time to repost I had this as well:
SELECT
*
FROM
[dbo].[TRAINING] A
LEFT OUTER JOIN [dbo].[xref_PG_TRAINING] B
ON A.[TRAINING_ID] = B.[TRAINING_ID]
AND B.PG_ID = 4
So now comes the other part the Case Statement. Let me attempt first and If I have issues I will repost. Thanks
February 2, 2010 at 7:54 am
Cool I got it. Haven't done SQL in awhile
SELECT
A.TRAINING_NAME,
A.MANDATORY,
CASE
WHEN B.DATE_COMPLETED IS NULL THEN 'Not Completed'
ELSE CONVERT(NVARCHAR(12),B.DATE_COMPLETED)
END
AS 'Completed'
FROM
[dbo].[TRAINING] A
LEFT OUTER JOIN [dbo].[xref_PG_TRAINING] B
ON A.[TRAINING_ID] = B.[TRAINING_ID]
AND B.PG_ID = 2
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply