question

How to create a combined view from 3 views? First view:- SELECT DISTINCT TOP (100) PERCENT f.FacilityGenId, f.FacilityNumberPrefix, f.FacilityNumber, f.FacilityName, ad.Address, ad.City, rc.CountyName, ad.ZipCode, ad.ZipCodeExt, ph.AreaCode, ph.PhoneNumber, f.InitialDateApprovedToOperate, f.TerminationDate, fl.EffectiveDate AS LicenseStart, fl.TerminationDate AS LicenseEnd, f.FacilityGenIdPreviousOwner, f.ConsultantGenId, f.AddressGenId, f.AddressSequence, rc.CountyGenId FROM dbo.Facility AS f INNER JOIN dbo.Campus AS c ON c.CampusGenId = f.CampusGenId INNER JOIN dbo.Applicant AS a ON a.ApplicantGenId = c.ApplicantGenId INNER JOIN dbo.ref_FacilityTypeDetail AS rftd ON rftd.FacilityTypeDetailGenId = a.FacilityTypeDetailGenId INNER JOIN dbo.ref_FacilityType AS rft ON rft.FacilityTypeGenId = rftd.FacilityTypeGenId INNER JOIN dbo.FacilityLicense AS fl ON f.FacilityGenId = fl.FacilityGenId LEFT OUTER JOIN dbo.Address AS ad ON f.AddressGenId = ad.AddressGenId AND ad.AddressSequence = (SELECT MAX(AddressSequence) AS Expr1 FROM dbo.Address AS ad2 WHERE (AddressGenId = ad.AddressGenId) AND (AddressTypeGenId = 1) AND (DeleteDate IS NULL)) LEFT OUTER JOIN dbo.Phone AS ph ON f.PhoneGenId = ph.PhoneGenId AND ph.PhoneSequence = (SELECT MAX(PhoneSequence) AS Expr1 FROM dbo.Phone AS p WHERE (PhoneGenId = ph.PhoneGenId) AND (PhoneTypeGenId = 1) AND (DeleteDate IS NULL)) LEFT OUTER JOIN dbo.ref_County AS rc ON ad.CountyGenId = rc.CountyGenId WHERE (rft.CentersInd = 1) AND (f.TerminationDate > GETDATE() OR f.TerminationDate IS NULL) AND (fl.FacilityLicenseSequence = (SELECT MAX(FacilityLicenseSequence) AS Expr1 FROM dbo.FacilityLicense AS fl2 WHERE (f.FacilityGenId = FacilityGenId) AND (EffectiveDate <= GETDATE()) AND (DeleteDate IS NULL))) AND (fl.EffectiveDate <= GETDATE()) AND (c.DeleteDate IS NULL) AND (a.DeleteDate IS NULL) AND (rftd.DeleteDate IS NULL) AND (rft.DeleteDate IS NULL) AND (ad.DeleteDate IS NULL) AND (ph.DeleteDate IS NULL) AND (f.DeleteDate IS NULL) AND (fl.DeleteDate IS NULL) ORDER BY f.FacilityGenId Second View: SELECT TOP (100) PERCENT f.FacilityGenId, f.AddressGenId, f.AddressSequence, f.FacilityNumberPrefix, f.FacilityNumber, f.FacilityNumberSuffix, f.FacilityName, f.Capacity, f.InitialDateApprovedToOperate, f.TerminationDate, ph.AreaCode, ph.PhoneNumber, rc.CountyName, ad.Address, ad.City, ad.ZipCode, c.CampusGenId, fl.EffectiveDate AS LicenseStart, fl.TerminationDate AS LicenseEnd, rc.CountyGenId FROM dbo.Facility AS f INNER JOIN dbo.Campus AS c ON f.CampusGenId = c.CampusGenId INNER JOIN dbo.Applicant AS a ON c.ApplicantGenId = a.ApplicantGenId INNER JOIN dbo.ref_FacilityTypeDetail AS rftd ON a.FacilityTypeDetailGenId = rftd.FacilityTypeDetailGenId INNER JOIN dbo.ref_FacilityType AS rft ON rftd.FacilityTypeGenId = rft.FacilityTypeGenId INNER JOIN dbo.FacilityLicense AS fl ON f.FacilityGenId = fl.FacilityGenId INNER JOIN dbo.ref_FacilityTypeMaster AS rftm ON rftm.FacilityTypeMasterGenId = rft.FacilityTypeMasterGenId LEFT OUTER JOIN dbo.Address AS ad ON f.AddressGenId = ad.AddressGenId AND ad.AddressSequence = (SELECT MAX(AddressSequence) AS Expr1 FROM dbo.Address AS ad2 WHERE (AddressGenId = ad.AddressGenId) AND (AddressTypeGenId = 1) AND (DeleteDate IS NULL)) LEFT OUTER JOIN dbo.Phone AS ph ON f.PhoneGenId = ph.PhoneGenId AND ph.PhoneSequence = (SELECT MAX(PhoneSequence) AS Expr1 FROM dbo.Phone AS p WHERE (PhoneGenId = ph.PhoneGenId) AND (PhoneTypeGenId = 1) AND (DeleteDate IS NULL)) LEFT OUTER JOIN dbo.ref_County AS rc ON ad.CountyGenId = rc.CountyGenId WHERE (rft.HomesInd = 1) AND (f.TerminationDate IS NULL OR f.TerminationDate > GETDATE()) AND (fl.FacilityLicenseSequence = (SELECT MAX(FacilityLicenseSequence) AS Expr1 FROM dbo.FacilityLicense AS fl2 WHERE (f.FacilityGenId = FacilityGenId) AND (EffectiveDate <= GETDATE()) AND (DeleteDate IS NULL))) AND (fl.EffectiveDate <= GETDATE()) AND (rftm.FacilityTypeMasterDescription = 'Child Care Home') AND (c.DeleteDate IS NULL) AND (a.DeleteDate IS NULL) AND (rftd.DeleteDate IS NULL) AND (rft.DeleteDate IS NULL) AND (ad.DeleteDate IS NULL) AND (ph.DeleteDate IS NULL) AND (f.DeleteDate IS NULL) AND (fl.DeleteDate IS NULL) ORDER BY f.FacilityGenId Third View: SELECT DISTINCT TOP (100) PERCENT f.FacilityGenId, f.FacilityNumberPrefix, f.FacilityNumber, f.FacilityName, ad.Address, ad.City, rc.CountyName, ad.ZipCode, ad.ZipCodeExt, ph.AreaCode, ph.PhoneNumber, f.TerminationDate, frr.ReRegistrationDate, frr.ReRegistrationApprovalDate, frr.ReRegistrationFromDate, frr.ReRegistrationToDate, f.ConsultantGenId, ad.AddressSequence, ad.AddressGenId, rc.CountyGenId FROM dbo.Facility AS f INNER JOIN dbo.Campus AS c ON c.CampusGenId = f.CampusGenId INNER JOIN dbo.Applicant AS ap ON ap.ApplicantGenId = c.ApplicantGenId INNER JOIN dbo.ref_FacilityTypeDetail AS rftd ON rftd.FacilityTypeDetailGenId = ap.FacilityTypeDetailGenId INNER JOIN dbo.ref_FacilityType AS rft ON rft.FacilityTypeGenId = rftd.FacilityTypeGenId INNER JOIN dbo.ref_FacilityTypeMaster AS rftm ON rftm.FacilityTypeMasterGenId = rft.FacilityTypeMasterGenId INNER JOIN dbo.FacilityReRegistration AS frr ON frr.FacilityGenId = f.FacilityGenId LEFT OUTER JOIN dbo.Address AS ad ON f.AddressGenId = ad.AddressGenId AND ad.AddressSequence = (SELECT MAX(AddressSequence) AS Expr1 FROM dbo.Address AS a WHERE (AddressGenId = ad.AddressGenId) AND (AddressTypeGenId = 1) AND (DeleteDate IS NULL)) LEFT OUTER JOIN dbo.Phone AS ph ON f.PhoneGenId = ph.PhoneGenId AND ph.PhoneSequence = (SELECT MAX(PhoneSequence) AS Expr1 FROM dbo.Phone AS p WHERE (PhoneGenId = ph.PhoneGenId) AND (PhoneTypeGenId = 1) AND (DeleteDate IS NULL)) INNER JOIN dbo.ref_County AS rc ON ad.CountyGenId = rc.CountyGenId WHERE (f.TerminationDate IS NULL OR f.TerminationDate > GETDATE()) AND (frr.SequenceNumber = (SELECT MAX(SequenceNumber) AS Expr1 FROM dbo.FacilityReRegistration AS frr2 WHERE (FacilityGenId = frr.FacilityGenId) AND (ReRegistrationFromDate <= GETDATE()) AND (DeleteDate IS NULL))) AND (rftm.FacilityTypeMasterDescription = 'Child Care Ministry') AND (c.DeleteDate IS NULL) AND (ap.DeleteDate IS NULL) AND (frr.DeleteDate IS NULL) AND (f.DeleteDate IS NULL) AND (ad.DeleteDate IS NULL) AND (ph.DeleteDate IS NULL) AND (rftd.DeleteDate IS NULL) AND (rft.DeleteDate IS NULL) AND (frr.ReRegistrationFromDate <= GETDATE()) ORDER BY f.FacilityGenId How will I combine these 3 views into one? Thanks, Hanoi