SQL Server query help needed for joining

Question

I have these three tables in SQL Server 2012.

CREATE TABLE [dbo].[ClientInfo]
(
    [ClientID] [int] IDENTITY(1,1) NOT NULL,
    [ClientName] [varchar](50) NULL,
    [ClientAddress] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [DOB] [date] NULL,
    [Country] [varchar](50) NULL,
    [Status] [bit] NULL,
    PRIMARY KEY (ClientID)
)

CREATE TABLE [dbo].[ClientInsuranceInfo]
(
    [InsID] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NULL,
    [InsTypeID] [int] NULL,
    [ActiveDate] [date] NULL,
    [InsStatus] [bit] NULL,    
    PRIMARY KEY (InsID)
)

CREATE TABLE [dbo].[TypeInfo]
(
    [TypeID] [int] IDENTITY(1,1) NOT NULL,
    [TypeName] [varchar](50) NULL,
    PRIMARY KEY (TypeID)
)    

Some sample data to execute the query

insert into ClientInfo (ClientName, State, Country, Status) 
values ('Lionel Van Praag', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Bluey Wilkinson', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Jack Young', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Keith Campbell', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Tom Phillis', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Barry Smith', 'NSW', 'Australia', 'True')
insert into ClientInfo (ClientName, State, Country, Status) 
values ('Steve Baker', 'NSW', 'Australia', 'True')

insert into TypeInfo (TypeName) values ('CarInsurance')
insert into TypeInfo (TypeName) values ('MotorcycleInsurance')
insert into TypeInfo (TypeName) values ('HeavyVehicleInsurance')

insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus)
values ('1', '1', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('1', '2', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('2', '1', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('2', '2', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('2', '3', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('3', '1', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('4', '1', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('4', '3', '2000-01-11', 'True')
insert into ClientInsuranceInfo (ClientID, InsTypeID, ActiveDate, InsStatus) 
values ('5', '2', '2000-01-11', 'True')

I have written the following query which returns only those clients who have 'MotorcycleInsurance' type:

select distinct 
    ClientInfo.ClientID, ClientInfo.ClientName, TypeInfo.TypeName
from 
    ClientInfo 
left join 
    ClientInsuranceInfo on ClientInfo.ClientID = ClientInsuranceInfo.ClientID
left join 
    TypeInfo on ClientInsuranceInfo.InsTypeID = TypeInfo.TypeID 
             and TypeInfo.TypeID = 2
where 
    typeinfo.TypeName is not null

But I want to do the following things

• I want to return all clients who have 'MotorcycleInsurance' along with rest of clients who do not have 'MotorcycleInsurance' as well.
• TypeName will be returned as NULL who do not have 'MotorcycleInsurance'
• ClientID have to be unique in the result set.
• Do not want to use UNION / UNION ALL.

How can I do this?

My required answer will be as follow

Answer 1

SELECT CI.ClientID, CI.ClientName, TI.TypeName
FROM dbo.ClientInfo AS CI
LEFT JOIN (SELECT ClientID, InsTypeID
           FROM dbo.ClientInsuranceInfo
           WHERE InsTypeID = 2 
           GROUP BY ClientID, InsTypeID ) AS CII ON CI.ClientID = CII.ClientID
LEFT JOIN dbo.TypeInfo AS TI  ON CII.InsTypeID = TI.TypeID;

Answer 2

Just remove your where clause and change join condition slightly

select distinct ClientInfo.ClientID, ClientInfo.ClientName, TypeInfo.TypeName
from ClientInfo 
left join ClientInsuranceInfo 
         on ClientInfo.ClientID = ClientInsuranceInfo.ClientID 
            and ClientInsuranceInfo.InsTypeID = 2 
left join TypeInfo on ClientInsuranceInfo.InsTypeID = TypeInfo.TypeID 

Answer 3

Try this one using ROW_NUMBER:

select a.ClientID, a.ClientName, a.TypeName
from 
(
select distinct ClientInfo.ClientID, ClientInfo.ClientName, TypeInfo.TypeName, ROW_NUMBER() over(partition by ClientInfo.ClientID order by case when TypeName is null then 1 else 0 end) as rn
from ClientInfo  
left join ClientInsuranceInfo on ClientInfo.ClientID = ClientInsuranceInfo.ClientID
left join TypeInfo on ClientInsuranceInfo.InsTypeID = TypeInfo.TypeID and TypeInfo.TypeID = 2
) a
where rn = 1

Edit: Updated ordering by case statement in Row_Number :)