DECLARE @ContactPerson TABLE (
Id INT,
Name NVARCHAR(50)
)
DECLARE @ContactEmail TABLE (
Id INT,
PersonId INT,
Email NVARCHAR(60),
PreferredInd INT
)
DECLARE @ContactPhone TABLE (
Id INT,
PersonId INT,
Phone NVARCHAR(20),
PreferredInd INT
)

DECLARE @ContactOrganization TABLE (
Id INT,
PersonId INT,
Organization NVARCHAR(60)
)

INSERT INTO @ContactPerson
SELECT 1, 'John Doe'

INSERT INTO @ContactEmail
        ( Id, PersonId, Email, PreferredInd )
SELECT 1, 1, '[email protected]', 0
UNION
SELECT 2, 1, '[email protected]', 1
UNION
SELECT 3, 1, '[email protected]', 0

INSERT INTO @ContactPhone
        ( Id, PersonId, Phone, PreferredInd )
SELECT 1, 1, '888-867-5309', 1
UNION
SELECT 2, 1, '305-476-5234', 0

INSERT INTO @ContactOrganization
        ( Id, PersonId, Organization )
SELECT 1, 1, 'US Government' UNION SELECT 2, 1, 'US Army'


SELECT DISTINCT cp.Id, cp.Name, Email, Phone, x2.Organization, x2.RowNum, ph.RowNum
FROM @ContactPerson cp
INNER JOIN (SELECT 3 NumberCount) Numbers ON Numbers.numberCount >= Numbers.numberCount
LEFT OUTER JOIN (SELECT DISTINCT cp.Id , Email , PreferredInd, ROW_NUMBER() OVER (ORDER BY ce.PreferredInd DESC, ce.Id) RowNum FROM @ContactPerson cp
    LEFT OUTER JOIN @ContactEmail ce ON cp.Id = ce.PersonId ) em ON cp.Id = em.Id
LEFT OUTER JOIN (SELECT DISTINCT cp.Id , PreferredInd, Phone, ROW_NUMBER() OVER (ORDER BY ph.PreferredInd DESC, ph.Id) RowNum FROM @ContactPerson cp
    LEFT OUTER JOIN @ContactPhone ph ON cp.Id = ph.PersonId ) ph ON cp.Id = ph.Id AND COALESCE(em.RowNum, ph.RowNum) = ph.RowNum AND COALESCE(em.PreferredInd, ph.PreferredInd) = ph.PreferredInd
LEFT OUTER JOIN (SELECT DISTINCT co.*, ROW_NUMBER() OVER (ORDER BY co.Organization) RowNum FROM @ContactPerson cp
                INNER JOIN @ContactOrganization co ON cp.Id = co.PersonId) x2 ON x2.PersonId = cp.Id AND COALESCE(em.RowNum, x2.RowNum) = x2.RowNum