CREATE TABLE [dbo].[SCOUTS]  ( 
    [SCT_ID]         	int IDENTITY(1,1) NOT NULL,
    [SCT_FNAME]      	nvarchar(25) NOT NULL,
    [SCT_LNAME]      	nvarchar(25) NOT NULL,
    [SCT_DOB]        	datetime NULL,
    [REF_SCT_RANK_ID]	int NOT NULL,
    [REF_DEN_ID]     	int NULL,
    [SCT_STATUS]     	varchar(8) NULL CONSTRAINT [DF_SCOUTS_SCT_STATUS]  DEFAULT ('ACTIVE'),
    CONSTRAINT [PK_SCOUTS] PRIMARY KEY([SCT_ID])
)
GO

CREATE TABLE [dbo].[xref_PG_SCT]  ( 
    [PG_ID] 	int NOT NULL,
    [SCT_ID]	int NOT NULL 
    )
GO
ALTER TABLE [dbo].[xref_PG_SCT]
    ADD CONSTRAINT [FK_xref_PG_SCT_SCOUTS]
	FOREIGN KEY([SCT_ID])
	REFERENCES [dbo].[SCOUTS]([SCT_ID])
	ON DELETE CASCADE 
	ON UPDATE CASCADE 
	NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[xref_PG_SCT]
    ADD CONSTRAINT [FK_xref_PG_SCT_PARENT_GUARDIAN]
	FOREIGN KEY([PG_ID])
	REFERENCES [dbo].[PARENT_GUARDIAN]([PG_ID])
	ON DELETE CASCADE 
	ON UPDATE CASCADE 
	NOT FOR REPLICATION 
GO

CREATE TABLE [dbo].[PARENT_GUARDIAN]  ( 
    [PG_ID]          	int IDENTITY(150,1) NOT NULL,
    [REF_PG_RELAT_ID]	int NULL,
    [PG_FNAME]       	nvarchar(25) NOT NULL,
    [PG_LNAME]       	nvarchar(25) NOT NULL,
    [PG_ADDRESS]     	nvarchar(50) NULL,
    [PG_CITY]        	nvarchar(25) NULL,
    [PG_STATE]       	varchar(2) NULL,
    [PG_ZIP]         	nvarchar(11) NULL,
    [PG_PPHONE]      	nvarchar(12) NULL,
    [PG_WPHONE]      	nvarchar(12) NULL,
    [PG_EPHONE]      	nvarchar(12) NULL,
    [PG_EMAILHOME]   	nvarchar(75) NULL,
    [PG_EMAILWORK]   	nvarchar(75) NULL,
    [PG_USERNAME]    	nvarchar(25) NULL,
    [PG_PASSWORD]    	nvarchar(25) NULL,
    [PG_STATUS]      	varchar(8) NOT NULL CONSTRAINT [DF__PARENT_GU__PG_ST__76619304]  DEFAULT ('ACTIVE'),
    [PG_INSERTDATE]  	datetime NOT NULL CONSTRAINT [DF__PARENT_GU__PG_IN__7755B73D]  DEFAULT (convert(varchar,getdate(),101)),
    [PG_LASTVISIT]   	nvarchar(25) NULL,
    [PG_PRIMARY]     	varchar(1) NULL,
    CONSTRAINT [PK_PARENT_GUARDIAN] PRIMARY KEY([PG_ID])
)
GO

-- FIRST LETS ADD SOME NEW SCOUNTS INTO THE TABLE
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS]) 
    VALUES(1, N'Joey', N'Smith', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS]) 
    VALUES(2, N'Billy', N'Corells', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS]) 
    VALUES(3, N'Dean', N'Corells', '', 1, 2, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS]) 
    VALUES(4, N'Frank', N'Galley', '', 5, 3, 'ACTIVE')
GO

-- NEXT LETS CREATE SOME PARENTS FOR EACH OF THE SCOUTS

INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(1, 0, N'Julie', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(2, 0, N'Dan', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(3, 0, N'David', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(4, 0, N'Sandra', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(5, 0, N'Bob', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS]) 
    VALUES(6, 0, N'Barbara', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO

-- NOW WE ARE GOOING TO JOIN EACH SCOUT TO EACH PARENT USING OUR CROSS REFERENCE TABLE
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(1, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(2, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(3, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(4, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(3, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(4, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(5, 4)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID]) 
    VALUES(6, 4)
GO

/*This sets your parents status with 
multiple children one of which is still ACTIVE and 
one of which is INACTIVE , to INACTIVE not what
is wanted but */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'INACTIVE'
WHERE EXISTS(SELECT p.PG_ID,s.SCT_STATUS,s.SCT_ID
FROM [PARENT_GUARDIAN] p 
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON  
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'INACTIVE')

/* We then find and place into a temporarty table 
those scouts that are still ACTIVE */

SELECT p.PG_ID  
INTO #T
FROM [PARENT_GUARDIAN] p 
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON  
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'ACTIVE'

/* Next we go back and set those parents to ACTIVE */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'ACTIVE'
FROM #T WHERE #T.PG_ID = PARENT_GUARDIAN.PG_ID

/* Check did we obtain the desired results  

PG_LName	PG_STATUS
Smith	         INACTIVE
Smith	         INACTIVE
Corells	         ACTIVE
Corells	         ACTIVE
Galley	         INACTIVE
Galley	         INACTIVE */

DROP TABLE #T