--The table that holds the Au Pair's languages
CREATE TABLE #AuPair(
	AuPairId int,
	PrimaryLanguage int,
	Age tinyint,
	Height tinyint,
	Smoker bit	
)
--The table that holds the Family's languages
CREATE TABLE #Family(
	FamilyId int,
	PrimaryLanguage int,
	MinAge tinyint,
	MaxAge tinyint,
	MinHeight tinyint,
	MaxHeight tinyint,
	Smoker tinyint -- 0 = No, 1 = Yes, 2 = Either
)
--The table that holds the other languages the Au Pair can speak
CREATE TABLE #AuPairLanguage(
	AuPairId int,
	LanguageId int
)
--The table that holds the languages the Family would like
CREATE TABLE #FamilyLanguage(
	FamilyId int,
	LanguageId int
)

--Insert the dummy data
INSERT INTO #AuPair (AuPairId, PrimaryLanguage, Age, Height, Smoker)
SELECT 1, 3, 30, 115, 0 UNION ALL 
SELECT 2, 3, 19, 100, 0 UNION ALL 
SELECT 3, 4, 22, 135, 1

INSERT INTO #Family (FamilyId, PrimaryLanguage, MinAge, MaxAge, MinHeight, MaxHeight, Smoker)
SELECT 1, 1, 0, 255, 100, 130, 0 UNION ALL 
SELECT 2, 2, 20, 25, 0, 255, 2 UNION ALL 
SELECT 3, 2, 0, 255, 0, 255, 1

INSERT INTO #AuPairLanguage (AuPairId, LanguageId)
SELECT 1, 1 UNION ALL 
SELECT 1, 2 UNION ALL 
SELECT 2, 1 UNION ALL 
SELECT 2, 2 UNION ALL 
SELECT 3, 2

INSERT INTO #FamilyLanguage (FamilyId, LanguageId)
SELECT 2, 3 UNION ALL 
SELECT 3, 1





DROP TABLE #AuPair
DROP TABLE #Family
DROP TABLE #UserInterests
DROP TABLE #FamilyLanguage

SELECT DISTINCT
	f.*,
	a.*
FROM
	#Family f
		LEFT JOIN #AuPair a
			ON 
				a.Age BETWEEN f.MinAge AND f.MaxAge
			AND
				a.Height BETWEEN f.MinHeight AND f.MaxHeight
			AND
				(f.Smoker > 0) OR (a.Smoker = 0)
		LEFT JOIN #AuPairLanguage al
			ON f.PrimaryLanguage = al.LanguageId
		LEFT JOIN #FamilyLanguage fl
			ON fl.LanguageId = al.LanguageId AND f.FamilyId = fl.FamilyId