USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myPhone99		(PhoneID 	int IDENTITY(1,1), PhoneNumber 	varchar(10))
CREATE TABLE myLastName99	(LastNameID 	int IDENTITY(1,1), LastName 	varchar(50))
CREATE TABLE myFirstName99	(FirstNameID 	int IDENTITY(1,1), FirstName	varchar(10))
CREATE TABLE myGender99		(GenderID 	int IDENTITY(1,1), Gender 	varchar(10))

CREATE TABLE myClient99(
	  ClientID	int IDENTITY(1,1)
	, PhoneID	int
	, LastNameID	int
	, FirstNameID	int
	, GenderId	int)
GO

INSERT INTO myGender99(Gender) SELECT 'Male' UNION ALL SELECT 'Female'
INSERT INTO myFirstName99(FirstName) SELECT 'Brett' UNION ALL SELECT 'Boy5' UNION ALL SELECT 'Tara'
INSERT INTO myLastName99(LastName) SELECT 'Kaiser' UNION ALL SELECT 'CSE' UNION ALL SELECT 'Duggan'
INSERT INTO myPhone99(PhoneNumber) SELECT '9735551212' UNION ALL SELECT '800551212'
GO

INSERT INTO myClient99(PhoneID, LastNameID, FirstNameID, GenderID)
SELECT 1,1,1,1 UNION ALL
SELECT 2,2,2,1 UNION ALL
SELECT 3,3,3,2 UNION ALL
SELECT 4,3,3,2 UNION ALL
SELECT 3,4,3,2 UNION ALL
SELECT 3,4,4,2 UNION ALL
SELECT 3,3,4,4
GO

-- Very useful, huh
SELECT * FROM myClient99
GO

-- Fun with surrogates...

   SELECT c.ClientID, p.PhoneNumber, l.LastName, f.FirstName, g.Gender
     FROM myClient99	c
LEFT JOIN myPhone99 	p ON c.PhoneID     = p.PhoneID
LEFT JOIN myLastName99 	l ON c.LastNameID  = l.LastNameID
LEFT JOIN myFirstName99 f ON c.FirstNameID = f.FirstNameID
LEFT JOIN myGender99	g ON c.GenderID    = g.GenderID
GO

SET NOCOUNT OFF
DROP TABLE myPhone99, myLastName99, myFirstName99, myGender99, myClient99
GO