USE [tempdb]
GO
--- create 4 tables in tempdb..all have "x_" prefis
---- for data integrity you could add FKs

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_Person]') AND type in (N'U'))
DROP TABLE x_Person

BEGIN
CREATE TABLE [x_Person](
	[PersonId] [int] IDENTITY(1,1) NOT NULL,
	[PersonName] [varchar](50) NOT NULL,
	[PersonDetails1] [varchar](50) NULL,
	[PersonDetails2] [varchar](50) NULL,
	[PersonDetails3] [varchar](50) NULL,
 CONSTRAINT [PK_x_Person] PRIMARY KEY CLUSTERED 
(
	[PersonId] ASC
)
)
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_EventType]') AND type in (N'U'))
DROP TABLE x_EventType
BEGIN
CREATE TABLE [x_EventType](
	[EventTypeID] [int] IDENTITY(1,1) NOT NULL,
	[EventTypeDetails] [varchar](50) NULL,
 CONSTRAINT [PK_x_EventType] PRIMARY KEY CLUSTERED 
(
	[EventTypeID] ASC
)
)
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_Event]') AND type in (N'U'))
DROP TABLE x_Event
BEGIN
CREATE TABLE [x_Event](
	[EventId] [int] IDENTITY(1,1) NOT NULL,
	[EventTypeID] [int] NOT NULL,
	[EventName] [varchar](50) NOT NULL,
	[EventDetails] [varchar](50) NULL,
 CONSTRAINT [PK_x_Event] PRIMARY KEY CLUSTERED 
(
	[EventId] ASC
)
)
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_EventPerson_xref]') AND type in (N'U'))
DROP TABLE x_Eventperson_xref

BEGIN
CREATE TABLE [x_EventPerson_xref](
	[PersonID] [int] NOT NULL,
	[EventID] [int] NOT NULL,
 CONSTRAINT [PK_x_EventPerson_xref] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC,
	[EventID] ASC
)
)
END
GO

--- insert some data

SET NOCOUNT ON
GO
SET IDENTITY_INSERT [dbo].[x_Person] ON
GO
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (1,'Matthew',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (2,'Mark',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (3,'Luke',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (4,'John',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (5,'Paul',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (6,'George',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (7,'John',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (8,'Ringo',NULL,NULL,NULL)
SET IDENTITY_INSERT [dbo].[x_Person] OFF
GO

SET IDENTITY_INSERT [dbo].[x_EventType] ON
GO
INSERT [dbo].[x_EventType]([EventTypeID],[EventTypeDetails]) VALUES (1,'Business')
INSERT [dbo].[x_EventType]([EventTypeID],[EventTypeDetails]) VALUES (2,'Social')
SET IDENTITY_INSERT [dbo].[x_EventType] OFF
GO

SET NOCOUNT ON
GO
SET IDENTITY_INSERT [dbo].[x_Event] ON
GO
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (1,1,'SQL Seminar',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (2,1,'AGM',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (3,1,'Monthly Planning',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (4,2,'BBQ',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (5,2,'Xmas Party',NULL)
SET IDENTITY_INSERT [dbo].[x_Event] OFF
GO

SET NOCOUNT ON
GO
INSERT [dbo].[x_EventPerson_xref] VALUES (1,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (3,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (3,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (5,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (5,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (7,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (7,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (8,5)


--- Business schedule query example

SELECT     dbo.x_Person.PersonName, dbo.x_EventType.EventTypeDetails, dbo.x_Event.EventName
FROM           dbo.x_Event INNER JOIN
                            dbo.x_EventPerson_xref ON dbo.x_Event.EventId = dbo.x_EventPerson_xref.EventID INNER JOIN
                            dbo.x_Person ON dbo.x_EventPerson_xref.PersonID = dbo.x_Person.PersonId INNER JOIN
                            dbo.x_EventType ON dbo.x_Event.EventTypeID = dbo.x_EventType.EventTypeID
WHERE       (dbo.x_EventType.EventTypeID = 1)
ORDER BY dbo.x_Person.PersonName, dbo.x_EventType.EventTypeDetails
GO

--- Who's not going to the Xmas Party?

;WITH CTE AS
(SELECT      PersonID
FROM           dbo.x_EventPerson_xref
WHERE       (EventID = 5)
)
SELECT    dbo.x_Person.PersonName
FROM           dbo.x_Person LEFT OUTER JOIN
                            CTE ON dbo.x_Person.PersonId = CTE.PersonID
WHERE       (CTE.PersonID IS NULL)