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
LEFT OUTER JOIN (SELECT DISTINCT cp.Id , Email , PreferredInd, ROW_NUMBER() OVER (ORDER BY ce.PreferredInd DESC, ce.Id) RowNum
        FROM @ContactPerson cp
        INNER 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
        INNER 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