• This article on OUTER JOINs should be helpful to you. Even though it is for SQL Server 2000 it still applies to 2014.

    Essentially you want something like this:

    SELECT

    C.firstName,

    C.lastName,

    CP.phone,

    CA.ADDRESS,

    CI.SSN,

    CI.Medicaid

    FROM

    dbo.consumer AS C

    LEFT JOIN dbo.consumerPhone AS CP

    ON C.consumerID = CP.consumerID

    LEFT JOIN consumerAddress AS CA

    ON C.consumerID = CA.consumerID

    LEFT JOIN consumerIdentifier AS CI

    ON C.consumerID = CI.consumerID;

    If there will always be a row in ConsumerIdentifier you can make that an INNER JOIN